How to apply loop in proc sql to create new variables in SAS

[tSAS1]

I want to use proc sql to create the maximum of each var by ID, and since I have many variables so I want to use the loop do.

I used the below code but I get only var100 , but I want all variables to be in the new_start table.

%macro m();

proc sql;

%do j=1 %to 100; 
create table new_start as select distinct ID, max(var&j.) as var&j.
from start
group by ID
order by ID;
%end;

quit;
%mend;

%m();

I want that  new_start dataset contains: ID var1 var2 var3 ..... var100

[tSAS1 · Accepted solution]

The code works in that way:

%macro m();

proc sql;

create table new_start as select distinct ID
%do j=1 %to 100;
, max(var&j.) as var&j.
%end;
from start
group by ID
order by ID;


quit;
%mend;

%m();

[PeterClemmensen]

So you have a dataset with the variables ID, var1, var2 ..., var100, correct? And you want the max value for each var within each ID?

Then Proc SQL is not the tool. And the macro language is definitely not.

[tSAS1]

Thank you, but it's working with %m() macro I created.

[PeterClemmensen]

Instead, do something like this

proc summary data = have;
   class ID;
   var var:;
   output out = want max =;
run;

[tSAS1]

Thank you it's working good, but I had to remove the first line from have dataset

[ballardw]

proc summary data = have nway;
   class ID;
   var var:;
   output out = want max =;
run;

I assume by your comment "I had to remove the first line from have dataset " that you actually meant the WANT data set, or whatever output you created from summary. The NWAY option means that you get only the highest combination of the Class variables. The first output record in Want above would have been the max across all levels of the ID varaibles. The automatic variable _type_ would be 0, indicating that it is that overall summary, with each of the ID values having _type_=1.

When there are more Class variables there are more combinations that may come into play. The ability to create many levels of summaries in one procedure call can be a very powerful tool as it means you can have ONE summary data set instead of dozens. Select which specific ones are wanted using the _type_ variable as needed.