In a report I've made, I have data showing for an individual, with one line for each year that there is data for that individual. When a person has more than one line of data, I want to take a field from the second line and have it show up on the first line (in effect, eliminating the second row from the report).

Example:

JoeBlow row 1 data1a data1b
JoeBlow row 2 data2a data2b

What I want is:

JoeBlow row1 data1a data1b data2a data2b

How might I go about doing something like this? Thank you in advance.

This message has been edited. Last edited by: webmeister,

SUM DATAA DATAB
BY NAME
ACROSS YEAR

Thank you Ginny,

At the risk of sounding dumb, the piece of code in my FEX that I am trying to do this is the part that prints my report. I'm currently using
TABLE FILE XYZ
PRINT
FIELDA FIELDB BY FIELDC
and so on

Can I change that to something like

TABLE FILE XYZ
SUM
FIELDA FIELDB ACROSS FIELDC

and get the results I'm hoping for?

Should I place my code on the forum for you to have alook? Would that help? Thank you so much for replying.

Webmeister

This highly depends upon the format of the fields FIELDA and FIELDB.
If these are numbers, you will proable get what you need, if they are strings you will get thw last value in the database.

Hi, Frank,

Thanks for answering.... my fields are numeric data but I think that'll be just fine. I'll tweak the report and see what comes out.

I appreciate your help.

APP FI DATAMAS DISK data.mas
-RUN
-WRITE DATAMAS FILENAME=DATAFILE,SUFFIX=FIX
-WRITE DATAMAS SEGNAME=DATA,SEGTYPE=S0
-WRITE DATAMAS FIELDNAME=NAME,ALIAS=NM,FORMAT=A15,ACTUAL=A15,$
-WRITE DATAMAS FIELDNAME=YEAR,ALIAS=YR,FORMAT=YY,ACTUAL=YY,$
-WRITE DATAMAS FIELDNAME=DATAA,ALIAS=DA,FORMAT=A6,ACTUAL=A6,$
-WRITE DATAMAS FIELDNAME=DATAB,ALIAS=DB,FORMAT=A6,ACTUAL=A6,$
APP FI DATA DISK data.ftm
-RUN
-WRITE DATA JoeBlow        2007data1adata1b
-WRITE DATA JoeBlow        2008data2adata2b
-WRITE DATA GinnyJakes     2007data3adata3b
-WRITE DATA FrankDutch     2008data4adata4b  
TABLE FILE DATA
SUM DATAA AS '' DATAB AS ''
BY NAME
ACROSS YEAR NOPRINT
END

WM, You are going to need a vertical sort so that the rows are distinguished from each other. In my example, I use NAME. If you don't have a field to sort BY, then you'll have to make one up using a DEFINE or COMPUTE.

Hopefully this example will get you on your way.

Thanks Ginny,

I'll play around with your suggestion. It's nice to have such knowledgeable help on the forum.

WebMeister,

quote:

JoeBlow row 1 data1a data1b
JoeBlow row 2 data2a data2b

In Ginny's example, there is an assumption that you have a field which characterizes the rows, namely YEAR. Maybe you do not have that. Maybe you do but, say, for JoeBlow the years ar 2007 and 2008, but for Danny, you have 2001 and 2009. Then if you sort ACROSS YEAR the values on the page will be peppered about.
So, to make this more general, I would do (using the CAR file):

  
TABLE FILE CAR
LIST RETAIL
BY CAR
ON TABLE HOLD
END
TABLE FILE HOLD
SUM RETAIL
BY CAR
ACROSS LIST NOPRINT
END

See if this answers your question.

And a thank you to you also, Danny!

quote:

with one line for each year that there is data

He told me he had a year in the first post!

But your suggestion is good as well, Danny.

I think I might have misled you all..... all I want to do is to take two or three fields from my second and subsequent lines of data and place those two or three fields onto row 1 of my data, which perhaps is not doable. This is what I'm looking for:

Joe Blow yr1 fld1A fld2A fld3A
Joe Blow yr2 fld1B fld2B fld3B
Joe Blow yr3 fld1C fld2C fld3C

Here's what I want to end up with:

Joe Blow yr1 fld1A fld2A fld3A yr2 fld3B yr3 fld3C

In short, not all fields, just some fields from subsequent rows to be placed onto the first row and then to not print anything except the first row per individual.

I think this is called transposing from a row to a column, but I'm not sure. Is what I'm trying possible - to take only a few fields from other rows and put those few fields onto an inital row?

Thanks again!

APP FI DATAMAS DISK data.mas
-RUN
-WRITE DATAMAS FILENAME=DATAFILE,SUFFIX=FIX
-WRITE DATAMAS SEGNAME=DATA,SEGTYPE=S0
-WRITE DATAMAS FIELDNAME=NAME,ALIAS=NM,FORMAT=A15,ACTUAL=A15,$
-WRITE DATAMAS FIELDNAME=YEAR,ALIAS=YR,FORMAT=A4,ACTUAL=A4,$
-WRITE DATAMAS FIELDNAME=DATAA,ALIAS=DA,FORMAT=A6,ACTUAL=A6,$
-WRITE DATAMAS FIELDNAME=DATAB,ALIAS=DB,FORMAT=A6,ACTUAL=A6,$
APP FI DATA DISK data.ftm
-RUN
-WRITE DATA JoeBlow        2007data1adata1b
-WRITE DATA JoeBlow        2008data2adata2b
-WRITE DATA GinnyJakes     2007data3adata3b
-WRITE DATA FranDutch      2008data4adata4b  
TABLE FILE DATA
PRINT YEAR DATAA DATAB
COMPUTE RECNO/I8=IF NAME NE LAST NAME THEN 1 ELSE RECNO+1;
BY NAME
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE HOLD
SUM YEAR DATAA AS '' DATAB AS ''
BY NAME
ACROSS RECNO NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
END

WM, We gave you lots of clues above. Here is a modification of my previous post that does what you want. You need to start thinking outside of the box.

Hi, Ginny,

And thanks for your last reply. I agree about thinking outide the box, except here at work they use very strong duct tape to keep you in the box, if you know what I mean. Plus working on about four report requests simultaneously is not too much fun, either.

In any case, I'll study your last reply and try to apply it to my "plight."

Thanks again.... I'm off for the day and will let you know tomorrow if my program now works.

WM,

Do your fields (field1, field2, field3) have the same format? If they don't, is it acceptable that they be given the same format?

If so, here is the beginnig of a solution:

  
-SET &ECHO=ALL;
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
LIST RETAIL_COST/I7 DEALER_COST/I7 SALES/I7
BY CAR
ON TABLE SAVE AS WM
END
RETYPE
-RUN
DEFINE FILE WM
LISTORDER/I2=LIST*10 + LIMIT;
END
TABLE FILE WM
SUM COST
BY CAR
ACROSS LISTORDER NOPRINT
WHERE ((E00 EQ 1) OR (LIMIT EQ 2 OR 3)); 
END

And the WM.mas file:

  
FILENAME=WM    , SUFFIX=FIX
 SEGMENT=WM
 FIELDNAME=CAR, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
 FIELDNAME=LIST, ALIAS=E00, USAGE=I5, ACTUAL=A05, $
 SEGMENT=WMDATA, PARENT=WM, OCCURS=VARIABLE
 FIELDNAME=COST, ALIAS=E02, USAGE=I7, ACTUAL=A07, $
 FIELDNAME=LIMIT, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $

This message has been edited. Last edited by: Danny-SRL,

Hello, Danny,

No, the fields do not all have the same format, and in actuality, I have quite a few fields to print across a sheet of paper in landscape format, so I can't give them all the same format.... some have 1 character only, while others are P9.2C format and others have date formats.

I'm going to try Ginny's suggestion when I get to work tomorrow morning and see what comes of it.

I appreciate your offer of help, though, and keep the ideas coming!