BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

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

15 REPLIES 15
Cynthia_sas
SAS Super FREQ
You would probably be better off using DICTIONARY.COLUMNS to get the columns you want based on their TYPE. You can query the tables directly without using PROC CONTENTS first.
http://www2.sas.com/proceedings/sugi30/070-30.pdf

There are several examples of how to use the DICTIONARY tables in the Macro Language 1 class.

Here's an example to get you started:
proc sql;
select name into :cname separated by '~'
from dictionary.columns
where libname="SASHELP" and memname="CLASS"
and type = 'char';

select name into :nname separated by '~'
from dictionary.columns
where libname="SASHELP" and memname="CLASS"
and type = 'num';
quit;

%put character vars are: &cname;
%put numeric vars are: &nname;

options mprint symbolgen;
proc freq data=sashelp.class;
table %scan(&cname,1,~) %scan(&cname,2,~);
run;

proc means data=sashelp.class;
var %scan(&nname,1,~) %scan(&nname,2,~) %scan(&nname,3,~) ;
run;

There are ways to make the program more dynamic. I leave it to you to study other macro techniques to do this.

cynthia
Astounding
PROC Star

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.

PGStats
Opal | Level 21

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;
PG
Astounding
PROC Star

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=&macrovar) MEAN STD MIN MAX N NMISS P1 P5 MEDIAN P95 P99 STACKODSOUTPUT;
VAR _numeric_;
RUN;
 
PROC FREQ Data=Have (keep=&macrovar);
Tables _character_ / NoCum Missing;
Ods Output OneWayFreqs=Want;
Run;
turcay
Lapis Lazuli | Level 10

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

Cynthia_sas
SAS Super FREQ
HI,

Have you tested this on a dataset with all numeric or all character variables?

What is the error message you are receiving?

cynthia
turcay
Lapis Lazuli | Level 10

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,

Astounding
PROC Star

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=&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;

 

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.

turcay
Lapis Lazuli | Level 10

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;

 

Astounding.png

 

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

 

ballardw
Super User

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.

 

Astounding
PROC Star

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).  

turcay
Lapis Lazuli | Level 10

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,

mlogan
Lapis Lazuli | Level 10
Hi PGStats,
Can you please tell me how do I write/paste codes in forum that look like SAS code rather than the plain text. Also I bookmarked some posts, where do I see those list that I bookmarked? Thanks.
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 3622 views
  • 23 likes
  • 7 in conversation