BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Balas
Fluorite | Level 6

Hi, I'm transposing the data with Proc summary and the code is as below -

 

data have;
input PT DT RULE_ID RULE_CAT;
cards;
1 10 20 20
1 10 21 20
1 10 22 20
1 10 23 20
1 11 20 20
1 11 21 20
1 11 22 20
1 11 23 20
1 12 20 20
1 12 21 20
1 12 22 20
1 12 23 20
2 10 20 20
2 10 21 20
2 10 22 20
2 10 23 20
2 11 20 20
2 11 21 20
2 11 22 20
2 11 23 20
2 12 20 20
2 12 21 20
2 12 22 20
2 12 23 20
;;;;
run;

proc sql noprint feedback;
select max(ptcount) into :dim
from (select count(PT) as ptcount from have group by PT, DT);
quit;
run;

OPTIONS SYMBOLGEN;
proc summary data=have nway;
class PT DT;
output out=need idgroup(out[&dim](RULE_ID: RULE_CAT: )=);
run;

 

With the above code my final dataset need looks like below.

 

Capture1.1.JPG

Capture1.2.JPG

 

So the question now is - columns need to be rearranged like, RULE_ID_1, RULE_CAT_1, RULE_ID_2, RULE_CAT_2,RULE_ID_3, RULE_CAT_3,RULE_ID_4, RULE_CAT_4.

 

Is this possible? Because the number of columns getting created is random and based on the value of the macro variable dim getting created from Proc Sql.

 

Please share your views and suggestions.

1 ACCEPTED SOLUTION

Accepted Solutions
Balas
Fluorite | Level 6

Thanks TomKari.

 

Your solution works absolutely well with the small datasets like the one provided.

 

But creating the lnter2 dataset with multi millions of records will take huge CPU time and memory where a column holds all the variable names needs to be transposed.

 

Can you please suggest - If I need to apply the same logic on huge datasets, do I need to tweak the logic or do we have any new logic to be built?

 

 

View solution in original post

5 REPLIES 5
Reeza
Super User

You may be interested in this macro from this paper that has a few methods built in to do what you're requiring.

 

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

TomKari
Onyx | Level 15

I think this is pretty close...

 

proc sort data=Have out=Inter1;
by PT DT RULE_ID;
run;

data Inter2;
Length CN $20;
retain Count;
keep PT DT CN DV;
set Inter1;
by PT DT;
if first.DT
then do;
  Count = 0;
end;
Count = Count + 1;
CN = "RULE_ID_"||compress(put(Count, best7.));
DV = RULE_ID;
output;
CN = "RULE_CAT_"||compress(put(Count, best7.));
DV = RULE_CAT;
output;
run;

PROC TRANSPOSE DATA=Inter2 OUT=Want;
 BY PT DT;
 ID CN;
 VAR DV;
RUN;

Balas
Fluorite | Level 6

Thanks TomKari.

 

Your solution works absolutely well with the small datasets like the one provided.

 

But creating the lnter2 dataset with multi millions of records will take huge CPU time and memory where a column holds all the variable names needs to be transposed.

 

Can you please suggest - If I need to apply the same logic on huge datasets, do I need to tweak the logic or do we have any new logic to be built?

 

 

TomKari
Onyx | Level 15

I've found that trying to predict performance is a mugs game.

 

I'd suggest giving it a try. I usually start with say 100,000 records, if I'm happy up it to a million, and just keep going up by factors of 10.

 

Tom

LinusH
Tourmaline | Level 20
Like many other times I would like to question the need for transposing to wide format. What kind of analysis /processing will use the result?
Like you said, a problem is the varying no of columns - and that will probably be true for the programs/queries to follow.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1063 views
  • 3 likes
  • 4 in conversation