Hello everyone,
Nowadays, I’m trying to create dynamic code process in my SAS project. I have a macro variable which includes 4 columns name(In my real data, it includes many columns, this is my sample code), some of these columns are numeric and some of the other columns are character, in this macro variable.
I want to seperate the character and numeric columns from each other in the macro variable. If I can do this, I want to send character type variables to the PROC FREQ procedure, and send to numeric type variables to the PROC MEANS procedure.
Is it possible to create this or similar structure in my foregoing sentences?
My sample code as below.
I would be so pleased, if I can get useful help from you.
Data Have;
Length Numeric1 8 Numeric2 8 Character1 $ 32 Character2 $ 32;
Infile Datalines Missover;
Input Numeric1 Numeric2 Character1 Character2;
Datalines;
0.2 0.4 1 0
0.3 0.5 1 2
0.4 0.8 3 1
0.6 0.9 0 2
0.2 0.1 4 3
0.3 0.1 3 2
0.7 0.1 1 4
0.1 0.8 3 3
;
Run;
%Let Variable=Numeric1 Numeric1 Character1 Character2;
OPTIONS NOLABEL;
ODS OUTPUT Summary=Result;
PROC MEANS DATA=Have MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR Numeric1 Numeric2;
RUN;
Ods Trace On;
PROC FREQ Data=Have;
Tables Character1 Character2/ NoCum Missing;
Ods Output OneWayFreqs=Want;
Run;
Ods Trace Off;
Thank you,
Lastly, I tried to seperate the varibles by using PROC CONTENTS OUT= but I'm stopped up, maybe it is not possible, or I need hep
%Let Variable = Numeric1 Numeric2 Character1 Character2;
Proc Contents Data=Have Out=Intermediate Noprint; Run;
Data Want ;
Set Intermediate /*END=Eof*/;
Where Type=2 And Name Contains "&Variable." ;
Run;
Thank you
If your program "understands" the order in which the variables were created, you can also use these statements:
var numeric1-numeric-character2;
tables numeric1-character-character2;
Not sure if that would lead to some easier approaches or not.
You know you could do:
OPTIONS NOLABEL;
ODS OUTPUT Summary=Result;
PROC MEANS DATA=Have MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
PROC FREQ Data=Have;
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want;
Run;
A variation on this works very well when you have a macro variable that contains a subset of the available variable names:
OPTIONS NOLABEL;
ODS OUTPUT Summary=Result;
PROC MEANS DATA=Have (keep=¯ovar) MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
PROC FREQ Data=Have (keep=¯ovar);
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want;
Run;
Hi again,
I would like to thank all of yor invaluable recommendations.
I benefit from both Cynthia's and PGStats&Astounding methods. When I do some tests on my sample code, I realized that there is a possibility to no character variable or numeric variable in macro variable. After that I tried to build IF statements in my code to prevent my foregoing problem but I need little bit help at this moment Can somebody can help me about my following sample, please?
I changed the sample considering the probability of no character variable;
Data Have;
Length Numeric1 8 Numeric2 8 Character1 $ 32 Character2 $ 32;
Infile Datalines Missover;
Input Numeric1 Numeric2 Character1 Character2;
Datalines;
0.2 0.4 1 0
0.3 0.5 1 2
0.4 0.8 3 1
0.6 0.9 0 2
0.2 0.1 4 3
0.3 0.1 3 2
0.7 0.1 1 4
0.1 0.8 3 3
;
Run;
%Let MacroVar=Numeric1 Numeric1;
Data Want;
Set Have (Keep=&MacroVar);
Run;
proc sql;
select name into :cname separated by ' '
from dictionary.columns
where libname="WORK" and memname="WANT"
and type = 'char';
select name into :nname separated by ' '
from dictionary.columns
where libname="WORK" and memname="WANT"
and type = 'num';
quit;
%put character vars are: &cname;
%put numeric vars are: &nname;
%Macro Control;
Options NoLabel;
%IF &nname NE " " %THEN %DO;
ODS OUTPUT Summary=Want2;
PROC MEANS DATA=Have (keep=&nname) MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
%END ;
%IF &cname NE " " %THEN %DO;
PROC FREQ Data=Have (keep=&cname);
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want3;
Run;
%END ;
%Mend;
%Control;
Thank you
Hello Cynthia,
Yes, I tested it with all numeric or all character but I got this error -> ERROR 23-7: Invalid value for the KEEP option
Because, if there is no character variable in the macro variable, then it doesn't create the cname macro variable therefore I got an error in %IF statement.
Also I have some doubt whether I performed Astounding's %IF example correct or not in my code.
I need to resolve these two steps.
Thank you,
First, let me give you a faster, simpler way to create &CNAME and &NNAME. This is especially useful when you have a macro variable that contains a list of the variables of interest.
proc contents data=have (keep=¯ovar) noprint out=_contents_ (keep=name type);
run;
proc sql;
select distinct name into : nname from _contents_ where type=1;
select distinct name into : cname from _contents_ where type=2;
run;
The KEEP= option within PROC CONTENTS creates the output data set based on the subset of variables.
Then consider how to tell if a macro variable is empty or not. There are a few methods, but this is the one I prefer:
%if %length(&cname) > 0 %then %do;
%if %length(&nname) > 0 %then %do;
You can read an entire paper on the subject. I prefer this method because of simplicity and the differences in effectiveness between the methods is very small.
Astounding,
Your sample is very useful, thank you
But in your method, I realized that if I also hold the Character variables in the macro variable -> "%Let MacroVar=Numeric1 Numeric2 Character1 Character2;" , it brings just Numeric1 and Character1, shouldn't it bring -> Numeric1 Numeric2 Character1 Character2 variables.
I atached an image as below;
Your code helped me but if I don't put any character variables into macro variable, the &cname macro variable isn't created, therefore, I got an error like this -> ERROR 23-7: Invalid value for the KEEP option.
Did I made myself clear? Can we handle it?
%Let MacroVar=Numeric1 Numeric2 /*Character1 Character2*/;
/*Data Want;*/
/*Set Have (Keep=&MacroVar);*/
/*Run;*/
/**/
/*proc sql;*/
/*select name into :cname separated by ' ' */
/*from dictionary.columns*/
/*where libname="WORK" and memname="WANT"*/
/*and type = 'char';*/
/**/
/*select name into :nname separated by ' '*/
/*from dictionary.columns*/
/*where libname="WORK" and memname="WANT"*/
/*and type = 'num';*/
/*quit;*/
/**/
/*%put character vars are: &cname;*/
/*%put numeric vars are: &nname;*/
Proc Contents Data=Have (Keep=&MacroVar) Noprint
Out=_Contents_ (Keep=Name Type);
Run;
Proc Sql;
Select Distinct name Into : nname From _Contents_ Where type=1;
Select Distinct name Into : cname From _Contents_ Where type=2;
Run;
%put character vars are: &cname;
%put numeric vars are: &nname;
%Macro Control;
Options NoLabel;
%If %Length(&nname) > 0 %Then %Do;
ODS OUTPUT Summary=Want2;
PROC MEANS DATA=Have (keep=&nname) MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
%END;
%If %Length(&cname) > 0 %Then %Do;
PROC FREQ Data=Have (keep=&cname);
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want3;
Run;
%END;
%Mend;
%Control;
Thank you
Initialize the variables to blank before using the proc sqld code assignment:
%let cname=;
%let nname=;
Proc Sql;
Select Distinct name Into : nname From _Contents_ Where type=1;
Select Distinct name Into : cname From _Contents_ Where type=2;
Run;
However you are not showing how the macro Control is being run in relation to the code that creates those macro variables. You may have a macro scope issue about creating variables in a section of code that treats them as local and hence are not available to %control.
Absolutely right. Sorry I forgot to mention it. When SQL extracts 0 records, any macro variables populated by data values are not created at all (rather than created and set to null).
Thank you very much everyone,
Following code meet my desired output, I got very useful responses, I'm not sure which response should I accept, my desired code as below.
So glad I have you 🙂
Data Have;
Length Numeric1 8 Numeric2 8 Character1 $ 32 Character2 $ 32;
Infile Datalines Missover;
Input Numeric1 Numeric2 Character1 Character2;
Datalines;
0.2 0.4 1 0
0.3 0.5 1 2
0.4 0.8 3 1
0.6 0.9 0 2
0.2 0.1 4 3
0.3 0.1 3 2
0.7 0.1 1 4
0.1 0.8 3 3
;
Run;
%Let ModelVar=Numeric1 Numeric2;
Data Both;
Set Have (Keep=&ModelVar);
Run;
%let cname=;
%let nname=;
proc sql;
select name into :cname separated by ' '
from dictionary.columns
where libname="WORK" and memname="BOTH"
and type = 'char';
select name into :nname separated by ' '
from dictionary.columns
where libname="WORK" and memname="BOTH"
and type = 'num';
quit;
%put character vars are: &cname;
%put numeric vars are: &nname;
%Macro Control;
Options NoLabel;
%If %Length(&nname) > 0 %Then %Do;
ODS OUTPUT Summary=Want2;
PROC MEANS DATA=Have (keep=&nname) MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
%END;
%If %Length(&cname) > 0 %Then %Do;
PROC FREQ Data=Have (keep=&cname);
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want3;
Run;
%END;
%Mend;
%Control;
Thank you,
Hi @mlogan,
This article on how to add SAS syntax to a SAS community post might help. https://communities.sas.com/t5/Getting-Started/How-to-add-SAS-syntax-to-your-post/ta-p/224394
Kind Regards,
Michelle
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.