DATA Step, Macro, Functions and more

Create data into subsets

Reply
Contributor
Posts: 70

Create data into subsets

[ Edited ]

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;

Super User
Posts: 24,026

Re: Array to loop column name variable wide to long

Contributor
Posts: 70

Column name looping and create dataset using the column name

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;

PROC Star
Posts: 631

Re: Column name looping and create dataset using the column name

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();
Thanks,
Suryakiran
Super User
Posts: 24,026

Re: Column name looping and create dataset using the column name

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/

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 177 views
  • 0 likes
  • 3 in conversation