Create data into subsets

[radha009]

Dear experts, Need help on looping column names (varlist) and create a new table with unique name and id.

proc contents data=patch.Windows_patch out=patch.contents noprint; run;

proc sql ;
   select name into :varlist separated by ' '
  from patch.contents where name like '%CHG%';
%let n=&obs;
quit;

proc sql;

do i = 1 to dim(Patch) ;
    create table Patch.Patch(i) as select unique a.Name, a.CI_ID, B.App, b.App_ID from Patch.Troux a, Patch.Win_servers b
        where a.CI_ID=b.App_ID and b.Patch(i) is not null order by a.Name;

end;
quit;

[Reeza]

Don't.

Use PROC TRANSPOSE.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

[radha009]

oops my subject got change on search.

Need help on looping column names (varlist) and create a new table with unique name and id.

proc contents data=patch.Windows_patch out=patch.contents noprint; run;

proc sql ;
   select name into :varlist separated by ' '
  from patch.contents where name like '%CHG%';
%let n=&obs;
quit;

proc sql;

do i = 1 to dim(Patch) ;
    create table Patch.Patch(i) as select unique a.Name, a.CI_ID, B.App, b.App_ID from Patch.Troux a, Patch.Win_servers b
        where a.CI_ID=b.App_ID and b.Patch(i) is not null order by a.Name;

end;
quit;

[SuryaKiran]

Hi,

Are you trying to subset the data based on the list you  have in the macro variable. Try something like this:

proc sql;
select distinct age into:Age_dist separated by ','
from sashelp.class;
quit;
options symbolgen mprint mlogic;
%PUT &Age_Dist;
%Macro Subset();
%DO i=1 %to %SYSFUNC(Countw("&Age_Dist"));
%LET var=%SCAN(%BQUOTE(&Age_Dist),&i,',');
proc sql;
create table DS_&Var as 
select distinct name,age
from sashelp.class
where age=&Var;
quit;
%End;
%MEND Subset;
%subset();

[Reeza]

Note the expert recommendation - don't do this. It makes your life harder down the road. 

If you're going to do it anyways, try the options here:

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/