topic Re: Separate Character and Numeric Variables in the Macro Variable in SAS Programming

Separate Character and Numeric Variables in the Macro Variable

turcay — Mon, 30 May 2016 22:37:03 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

Cynthia_sas — Tue, 31 May 2016 00:19:17 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

Astounding — Tue, 31 May 2016 01:42:52 GMT

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.

Re: Separate Character and Numeric Variables in the Macro Variable

PGStats — Tue, 31 May 2016 02:43:32 GMT

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;

Re: Separate Character and Numeric Variables in the Macro Variable

Astounding — Tue, 31 May 2016 03:35:33 GMT

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;

Re: Separate Character and Numeric Variables in the Macro Variable

turcay — Tue, 31 May 2016 21:47:52 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

Cynthia_sas — Tue, 31 May 2016 22:38:01 GMT

HI,

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

What is the error message you are receiving?

cynthia

Re: Separate Character and Numeric Variables in the Macro Variable

Astounding — Tue, 31 May 2016 22:38:30 GMT

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.

Re: Separate Character and Numeric Variables in the Macro Variable

turcay — Tue, 31 May 2016 23:06:00 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

turcay — Tue, 31 May 2016 23:13:14 GMT

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,

Re: Separate Character and Numeric Variables in the Macro Variable

ballardw — Tue, 31 May 2016 23:22:45 GMT

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.

Re: Separate Character and Numeric Variables in the Macro Variable

Astounding — Wed, 01 Jun 2016 00:03:19 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

turcay — Wed, 01 Jun 2016 09:38:10 GMT

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,

Re: Separate Character and Numeric Variables in the Macro Variable

mlogan — Thu, 16 Jun 2016 12:57:22 GMT

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.

Re: Separate Character and Numeric Variables in the Macro Variable

MichelleHomes — Thu, 16 Jun 2016 13:31:15 GMT

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

Re: Separate Character and Numeric Variables in the Macro Variable

MichelleHomes — Thu, 16 Jun 2016 13:34:00 GMT

Hi @mlogan,

To see your bookmarks, select the arrow next to your name in the top right area when logged in and select My Subscriptions. You will see My Bookmarks listed on the page.

Kind Regards,

Michelle