Hi All, I'm very new to SAS programming and I got a requirement to transpose multi variables from the csv file. I found a solution in SAS community through PROC SUMMARY and i somehow manipulated the order of output variables through PROC SQL as per the requirement. But i would like to know if it's possible to produce this output without manipulation at PROC SQL step. Version: SAS 9.4 Input Data: RANK6_CODE|RANK_CODE|RANK_DESC|RANK_KEY 1|467|Sample_Description0|Rank_1 1|120|Sample_Description1|Rank_2 1|456|Sample_Description2|Rank_3 1|778|Sample_Description3|Rank_4 1|780|Sample_Description4|Rank_5 1|402|Sample_Description5|Rank_6 Output Data: RANK6_CODE|RANK6_DESC|RANK5_CODE|RANK5_DESC|RANK4_CODE|RANK4_DESC|RANK3_CODE|RANK3_DESC|RANK2_CODE|RANK2_DESC|RANK1_CODE|RANK1_DESC 402|Sample_Description5|780|Sample_Description4|778|Sample_Description3|456|Sample_Description2|120|Sample_Description1|467|Sample_Description Note: I have added the sample values through Datalines as requested. Code: DATA PROJECT.IMPORT;
INPUT @1 RANK6_CODE RANK_CODE $3-5 RANK_DESC $7-26 RANK_KEY $27-32;
DATALINES;
1 467 Sample_Description0 Rank_1
1 120 Sample_Description1 Rank_2
1 456 Sample_Description2 Rank_3
1 778 Sample_Description3 Rank_4
1 780 Sample_Description4 Rank_5
1 402 Sample_Description5 Rank_6
3 467 Sample_Description0 Rank_1
3 120 Sample_Description1 Rank_2
3 456 Sample_Description2 Rank_3
3 778 Sample_Description3 Rank_4
3 780 Sample_Description4 Rank_5
3 402 Sample_Description5 Rank_6
;
RUN;
proc sort data=PROJECT.IMPORT;
by RANK6_CODE;
run;
proc summary data=PROJECT.IMPORT;
CLASS RANK6_CODE;
OUTPUT OUT=wide(drop=_:)idgroup(out[6](RANK_DESC RANK_CODE )=);
/*Manually hard coded the number of Rank occurrence to 6*/ /*I can pass the value of occurrence by finding out the maximum count and assigning it to a local variable in OUT option - out[&variable]*/
run;
PROC PRINT DATA=work.wide;
RUN;
PROC SQL;
CREATE TABLE PROJECT.REQ1 AS
SELECT RANK_CODE_6,RANK_DESC_6,RANK_CODE_5 ,RANK_DESC_5 ,RANK_CODE_4 ,RANK_DESC_4 ,
RANK_CODE_3 ,RANK_DESC_3 ,RANK_CODE_2 ,RANK_DESC_2 ,RANK_CODE_1 , RANK_DESC_1
FROM WORK.WIDE WHERE RANK6_CODE IS NOT NULL;
/*Manually changed the order of retrieval through PROC SQL,Is there any way to do this in a different way?*/
/*I'm getting a record with NULL as RANK6_CODE with this query,why is that?*/
RUN;
QUIT;
PROC DATASETS NOLIST NODETAILS;
CONTENTS DATA=PROJECT.REQ1 OUT=WORK.details;
RUN;
PROC PRINT DATA=PROJECT.REQ1;
RUN;
Please let me know if you have any questions. I'm sure i must have made some mistakes here as I'm a beginner. So please point it out so that i can learn. Thanks!
... View more