|
View previous topic :: View next topic
|
| Author |
Message |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi,
I have a input SAS dataset as shown below,
| Code: |
depot region jobname elapsed
HI MVSD BK10 2.32 MINUTES
HI MVSD BK15 0.03 MINUTES
HI MVSD EODB 1.02 MINUTES
LC MVSE BK10 2.32 MINUTES
LC MVSE BK15 0.03 MINUTES
LC MVSE EODB 1.02 MINUTES |
The requirement is to convert this dataset to single variable for each occurence of depot variable, as shown below,
| Code: |
depot region jobname elapsed jobname elapsed jobname elapsed
HI MVSD BK10 2.32 MINUTES BK15 0.03 MINUTES EODB 1.02 MINUTES
LC MVSE BK10 2.32 MINUTES BK15 0.03 MINUTES EODB 1.02 MINUTES |
I have tried my luck with PROC TRANSPOSE, but unable to solve this. 
Could you please shed some light in how to go about this problem pls.
P.S. - I battled with the screen prints above for 15 mins and somehow aligned them a little.
Thanks in advance, |
|
| Back to top |
|
 |
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
PROC TRANSPOSE flips columns and rows -- not at all what you want.
Are there always three observations per depot variable, or can the number vary? If it can vary, what is the maximum number you expect?
This can be done with arrays or with named variables but you haven't provided enough information yet. |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Robert,
Thanks for the swift response, there are only 3 observations for each occurence of depot variable.
The region column will be the same for the 3 observations under each occurence of depot variable.
I am trying to construct a CSV report from the output dataset which needs to be sent as attachment.
I am ok with the emailing part, but the reformatting thing is a little confusing  as I have not used SAS for quite a while.
Pls let me know if any more info is required.
Thanks in advance, |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
For things like this, I usually use a building process as shown below.
| Code: |
//CARDSIN DD *
HI MVSD BK10 2.32 MINUTES
HI MVSD BK15 0.03 MINUTES
HI MVSD EODB 1.02 MINUTES
LC MVSE BK10 2.32 MINUTES
LC MVSE BK15 0.03 MINUTES
LC MVSE EODB 1.02 MINUTES
/*
//SYSIN DD *
OPTIONS NOCENTER SOURCE SOURCE2;
DATA OUT01;
INFILE CARDSIN;
INPUT @01 DEPOT $CHAR2.
@09 REGION $CHAR4.
@19 JOBN $CHAR4.
@26 TIMER $CHAR14.;
RUN;
PROC SORT DATA=OUT01 NODUPS;
BY DEPOT REGION;
RUN;
DATA _NULL_;
LENGTH CDEOUT $80.;
RETAIN CDEOUT ' ';
FILE FILEOUT;
SET OUT01;
BY DEPOT REGION;
IF FIRST.REGION THEN DO;
CDEOUT = DEPOT || ',' || REGION;
END;
ABCOUT = ','||TRIM(JOBN) || ',' || TRIM(TIMER);
CDEOUT = TRIM(CDEOUT) || TRIM(ABCOUT);
IF LAST.REGION THEN DO;
PUT CDEOUT;
CSVOUT = ' ';
END;
|
And this is the output - csv format
| Code: |
HI,MVSD,BK10,2.32 MINUTES,BK15,0.03 MINUTES,EODB,1.02 MINUTES
LC,MVSE,BK10,2.32 MINUTES,BK15,0.03 MINUTES,EODB,1.02 MINUTES
|
|
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Code: |
DATA INDATA;
INFILE ????????;
INPUT DEPOT $ REGION $ JOB $ ELAPSED ;
DATA MERGED;
SET INDATA ;
BY DEPOT;
RETAIN DEPOT R1 J1 E1 R2 J2 E2 ;
IF FIRST.DEPOT THEN DO; R1=REGION; J1=JOB; E1=ELAPSED; END;
ELSE IF LAST.DEPOT THEN DO; R3=REGION; J3=JOB; E3=ELAPSED; OUTPUT; END;
ELSE DO; R2=REGION; J2=JOB; E2=ELAPSED; END;
|
will generate a SAS data set MERGED with one observation per depot (assuming you replace the ???????? with the file DD). |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Many Thanks Expat & Robert for the smart logic.
It WORKED!!!! 
Regards, |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
Glad to hear it worked.  |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Robert Sample wrote: |
| Glad to hear it worked. |
Hey Robert, that is so sneaky, writing some code and then getting someone on the forum to test it for you  |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
TANSTAAFTesting too.. it will be 10$ pls.
 |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| expat, me? sneaky? Actually, that code was tested before I posted it -- so I knew it worked! |
|
| Back to top |
|
|
|
|
