topic Re: Need to transpose data with multiple variables in a better way in SAS Programming

Dynamically sort the column variables in the desired order without PROC SQL manipulation

Scott177 — Wed, 23 Oct 2019 14:05:43 GMT

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:

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!

Re: Need to transpose data with multiple variables in a better way

ScottBass — Wed, 23 Oct 2019 06:40:05 GMT

Post your data as self-contained data steps, using datalines, that we can cut-and-paste into SAS. Use Insert SAS Code (running man icon) to do so.

Your mangled cut-and-paste of your input and output data makes it hard for us to help you.

Re: Need to transpose data with multiple variables in a better way

Scott177 — Wed, 23 Oct 2019 09:28:42 GMT

Thanks ScottBass for the suggestion. I have modified the code, please let me know if i missed anything

Re: Dynamically sort the column variables in the desired order without PROC SQL manipulation

art297 — Wed, 23 Oct 2019 14:19:17 GMT

The code you posted doesn't match your data, but I think I know what you were trying to accomplish.

If your actual code was correct, then the following might be what you're seeking.

The reason you got the blank record is because you didn't include an NWAY option on your proc summary statement.

I used the macro you can copy and paste from https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-a-dataset/ta-p/433620

I used it because running it replaces everything in your code except for the initial datastep. I added one line to that datastep in order to only keep the numbers from you rank_key field.

Here is the code I ran:

libname project '/folders/myfolders';
DATA PROJECT.IMPORT;
  INPUT RANK6_CODE RANK_CODE $ RANK_DESC $ RANK_KEY $;
  rank_key=compress(rank_key, , 'kd');
  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 567 Sample_Description0 Rank_1
3 220 Sample_Description1 Rank_2
3 556 Sample_Description2 Rank_3
3 878 Sample_Description3 Rank_4
3 880 Sample_Description4 Rank_5
3 502 Sample_Description5 Rank_6
;
RUN;

%transpose(data=PROJECT.IMPORT, out=PROJECT.outputfile,
  by=rank6_code, id=rank_key,sort=yes, descendingid=yes,
  delimiter=_, autovars=all)

Art, CEO, AnalystFinder.com