How to create a list that will be iterated in the future

[x3d1m4]

Hi. Is there any good method for creating a list of numeric variables? Below I put some sample code to show what I want to do.

data my_dataset;
set sashelp.cars;
run;
proc contents data=my_loop noprint out=names_info (keep= type name varnum) ;
run;
proc sort data = names_info out = variable_names(keep = name);
     by varnum; where type=1;
run;

I want to create the int list variables. I need use them in future to loop through list.

%let my_work_variables = Cylinders EngineSize Horsepower Invoice Length MPG_City MPG_Highway MSRP Weight Wheelbase;

Thanks in advance for hint, solution : )

[Reeza]

This is great reference, with your exact problem.
https://support.sas.com/resources/papers/proceedings17/1516-2017.pdf

[x3d1m4]

I implemented from document syntax, so how i can from logs to one variable.

1                                                            System SAS                             19:33 Thursday, January 21, 2021

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Test_from book';
4          %LET _CLIENTPROCESSFLOWNAME='Przebieg procesu';
5          %LET _CLIENTPROJECTPATH='D:\Education\Big Data\Wyklady\1 semestr\Sas\Praca zaliczeniowa\3 zadanie\Projekt_sas.egp';
6          %LET _CLIENTPROJECTPATHHOST='MSI';
7          %LET _CLIENTPROJECTNAME='Projekt_sas.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=ACTIVEX;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=EGDefault
17             STYLESHEET=(URL="file:///D:/Education/Big%20Data/SAS/Sas%20soft/SASEnterpriseGuide/7.1/Styles/EGDefault.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         %macro varlist(dataset=);
27         proc contents data=&dataset out=cont noprint;
28         run;
29         proc sql noprint;
30         select distinct name into :varname1-:varname999 from cont;
31         quit;
32         %do i= 1 %to &sqlobs;
33         %put &i &&varname&i;
34         %end;
35         %mend varlist;
36         %varlist(dataset=sashelp.cars);

NOTE: The data set WORK.CONT has 15 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

1 Cylinders
2 DriveTrain
3 EngineSize
4 Horsepower
5 Invoice
6 Length
7 MPG_City
8 MPG_Highway
9 MSRP
2                                                            System SAS                             19:33 Thursday, January 21, 2021

10 Make
11 Model
12 Origin
13 Type
14 Weight
15 Wheelbase
37         
38         GOPTIONS NOACCESSIBLE;
39         %LET _CLIENTTASKLABEL=;
40         %LET _CLIENTPROCESSFLOWNAME=;
41         %LET _CLIENTPROJECTPATH=;
42         %LET _CLIENTPROJECTPATHHOST=;
43         %LET _CLIENTPROJECTNAME=;
44         %LET _SASPROGRAMFILE=;
45         %LET _SASPROGRAMFILEHOST=;
46         
47         ;*';*";*/;quit;run;
48         ODS _ALL_ CLOSE;
49         
50         
51         QUIT; RUN;
52         

To next form: 

%let list_of_variables = Cylinders DriveTrain EngineSize Horsepower Invoice Length MPG_City MPG_Highway MSRP Make Model Origin Type Weight Wheelbase;

[Reeza]

That creates a macro variable for every variable name, not a single macro variable list.

Either of the latter links posted by myself or FreelanceReinhard will generate what you want.

Or change the following part of your code:
proc sql noprint;
30 select distinct name into :varname1-:varname999 from cont;
31 quit;

to
proc sql noprint;
select distinct name into :varList separated by " " from cont;
quit;

%put &varList.;


Note that reading from dictionary tables is definitely simpler code, but if you have big libraries it can also be time & resource intensive.

[x3d1m4]

I change the code to next code:

%macro varList(dataset=);
proc contents data=&dataset out=cont noprint;
run;
proc sql noprint;
select distinct name into :varList separated by " " from cont;
quit;
%put &varList;
%mend;
%varList(dataset=sashelp.cars);

But excample and excample_1 not the same.

%let excample= &varList;
%let excample_1 =cylinders horsepower;

I think i do for sure something wrong. Regard of my experience i cannot detect what is going on.

Thanks for your time 🙂

[Tom]

If the target macro variable, VARLIST, does not exist then by default it will be created as LOCAL to the macro, %varlist, and will disappear when the macro finishes.

So either define the macro variable in advance:

%let varlist= ;
%varList(dataset=sashelp.cars);

Or modify your code to make the macro variable GLOBAL if it does not already exist.

%macro varList(dataset=);
%if not %symexist(varlist) %then %global varlist;
...

[x3d1m4]

I know what i miss.

%let varList=;
%macro varList(dataset=);
proc contents data=&dataset out=cont noprint;
run;
proc sql noprint;
select distinct name into :varList separated by " " from cont;
quit;
%do i= 1 %to &sqlobs;
%put &varList;
%end;
%mend;

If the target macro variable, VARLIST, does not exist then by default it will be created as LOCAL to the macro, %varlist, and will disappear when the macro finishes.

Thanks a lot @Tom  !

[x3d1m4]

---

@Reeza wrote:
That creates a macro variable for every variable name, not a single macro variable list.

Either of the latter links posted by myself or FreelanceReinhard will generate what you want.

Or change the following part of your code:
proc sql noprint;
30 select distinct name into :varname1-:varname999 from cont;
31 quit;

to
proc sql noprint;
select distinct name into :varList separated by " " from cont;
quit;

%put &varList.;


Note that reading from dictionary tables is definitely simpler code, but if you have big libraries it can also be time & resource intensive.

---

@Reeza Thank You for guiding 😁

[Tom]

If the list is short enough you can use one macro variable with a delimiter. 

select distinct name into :varnames separated by '|' from cont;
%let nvars=&sqlobs;
...
%do index=1 %to &nvars;
   %let varname = %scan(&varnames,&index,|);
   ...
%end;

Generating multiple macro variables usually just makes the problem harder, but there is a 64K byte limit on the size of a single macro variable.  In that case instead of making multiple macro variables it might be easier to just leave the values in a dataset and use a data step to drive the looping.  For example by using CALL EXECUTE().

create table varnames as select distinct name from cont ;
...
data _null_;
  set varnames ;
  call execute(cats('%nrstr(%mymacro)(name=',name,')'));
run;

[Reeza]

Example 5: Place All SAS Data Set Variables into a Macro Variable
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n0n...

[FreelanceReinh]

Hi @x3d1m4,

You can also retrieve such a list from DICTIONARY.COLUMNS:

proc sql noprint;
select name into :my_work_variables separated by ' '
from dictionary.columns
where libname='WORK' & memname='MY_DATASET' & type='num'
order by name;
quit;

%put &=my_work_variables;

It would be easy to modify this PROC SQL step so as to produce, e.g., a comma-separated list of quoted names of variables satisfying additional criteria regarding their names, lengths, labels, formats, etc. in a very specific sort order.