topic Re: Column name looping and create dataset using the column name in SAS Programming

Create data into subsets

radha009 — Tue, 17 Apr 2018 02:23:03 GMT

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;

Re: Array to loop column name variable wide to long

Reeza — Mon, 16 Apr 2018 23:09:44 GMT

Don't.

Use PROC TRANSPOSE.

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

Column name looping and create dataset using the column name

radha009 — Mon, 16 Apr 2018 23:22:10 GMT

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;

end;
quit;

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

SuryaKiran — Tue, 17 Apr 2018 01:30:42 GMT

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();

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

Reeza — Tue, 17 Apr 2018 02:24:48 GMT

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/