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 : )
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;
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 🙂
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;
...
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 !
@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 😁
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.