topic Need to create a macro variable from a dataset in SAS Programming

Need to create a macro variable from a dataset

ambadi007 — Tue, 28 Jul 2020 14:57:47 GMT

I have 2 datasets named named Test1 and Test2

Test1 is having no records now and i need to create a macro variable (cat1) from test1 with a value cat1=0 since this has no records , but in future if data comes that macro value should be cat1=1.

similarly test2 has data so this macro variable name should be cat2=1 ,

also if no data in future I should get a macro which cat2=0

like this the code should be dynamic ...

Any help appreciated ...

Re: Need to create a macro variable from a dataset

Reeza — Tue, 28 Jul 2020 15:08:36 GMT

%macro drive(index);
%let dsid=%sysfunc(open(test&index));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    %let var&index = 1;
   %end;
   %else %do;
        %let var&index = 0;
   %end;
 %end;
 %else %put test&index cannot be open.;
%mend drive;

%drive(1)
%drive(2)

Untested and modified from here - which has full details of the macro.

@ambadi007 wrote:

I have 2 datasets named named Test1 and Test2

Test1 is having no records now and i need to create a macro variable (cat1) from test1 with a value cat1=0 since this has no records , but in future if data comes that macro value should be cat1=1.

similarly test2 has data so this macro variable name should be cat2=1 ,

also if no data in future I should get a macro which cat2=0

like this the code should be dynamic ...

Any help appreciated ...

Re: Need to create a macro variable from a dataset

Kurt_Bremser — Tue, 28 Jul 2020 15:11:39 GMT

%macro create_cat(dsnumber);
%global cat&dsnumber.;
%let cat&dsnumber. = 0;
%if %sysfunc(exist(test&dsnumber.))
%then %do;
  data _null_;
  set test&dsnumber.;
  call symputx("cat&dsnumber.","1");
  stop;
  run;
%end;
%mend;

If the dataset has no observations, the data step terminates before it reaches the CALL SYMPUTX. Otherwise it reads just one observation.

Re: Need to create a macro variable from a dataset

PaigeMiller — Tue, 28 Jul 2020 15:12:24 GMT

@ambadi007 wrote:

I have 2 datasets named named Test1 and Test2

Test1 is having no records now and i need to create a macro variable (cat1) from test1 with a value cat1=0 since this has no records , but in future if data comes that macro value should be cat1=1.

similarly test2 has data so this macro variable name should be cat2=1 ,

also if no data in future I should get a macro which cat2=0

proc sql;
	select count(*)>0 into :cat1 from test1;
quit;

like this the code should be dynamic ...

dynamic with respect to what? What is changing that would require dynamic code?

Re: Need to create a macro variable from a dataset

Astounding — Tue, 28 Jul 2020 15:19:19 GMT

Just double-checking, since the solutions would be quite different.

You have clearly stated that you are looking to see if the data set exists, but has zero observations.

Is it possible that you really need to check for whether the data set doesn't exist at all?

Re: Need to create a macro variable from a dataset

Tom — Tue, 28 Jul 2020 15:20:34 GMT

A simple data step will do that. Make sure to code in a way that works when there are zero observations.

%let cnt1=0;
data _null_;
  set test1;
  call symputx('cnt1','1');
  stop;
run;

Re: Need to create a macro variable from a dataset

ambadi007 — Tue, 28 Jul 2020 15:28:15 GMT

I tried the code but Iam getting if the data is available i.e the cat1=1 but if no records Iam not getting the zero value in the macro variable

@Reeza wrote:
%macro drive(index);
%let dsid=%sysfunc(open(test&index));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    %let var&index = 1;
   %end;
   %else %do;
        %let var&index = 0;
   %end;
 %end;
 %else %put test&index cannot be open.;
%mend drive;

%drive(1)
%drive(2)
Untested and modified from here - which has full details of the macro.

@ambadi007 wrote:
I have 2 datasets named named Test1 and Test2
Test1 is having no records now and i need to create a macro variable (cat1) from test1 with a value cat1=0 since this has no records , but in future if data comes that macro value should be cat1=1.

similarly test2 has data so this macro variable name should be cat2=1 ,
also if no data in future I should get a macro which cat2=0

like this the code should be dynamic ...

Any help appreciated ...

Re: Need to create a macro variable from a dataset

ambadi007 — Tue, 28 Jul 2020 15:32:51 GMT

I just need two macro variable if the dateset is empty then cat1=0 else cat1=1

like wise for another dataset if no records cat2=0 else cat2=1

because i need to use these macros variable later in my code

Re: Need to create a macro variable from a dataset

PaigeMiller — Tue, 28 Jul 2020 15:51:46 GMT

I just need two macro variable if the dateset is empty then cat1=0 else cat1=1

like wise for another dataset if no records cat2=0 else cat2=1

So now you say you just need these two macro variables, and not others? Many samples of code have been provided, I'm sure some of them must work (like the code I provided). Have you tried them?

Re: Need to create a macro variable from a dataset

ambadi007 — Tue, 28 Jul 2020 15:55:51 GMT

it worrked which has data ... but for getting cat1=0 that time it is not working

Re: Need to create a macro variable from a dataset

Reeza — Tue, 28 Jul 2020 15:56:58 GMT

%macro drive(index);

%global var&index;

%let dsid=%sysfunc(open(test&index));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    %let var&index = 1;
   %end;
   %else %do;
        %let var&index = 0;
   %end;
 %end;
 %else %let var&index = 0;;
%mend drive;

%drive(1)
%drive(2)

%put var1.;
%put var2.;

FYI - most likely this would be easier to do at the end of whatever other step is creating the multiple data sets. What makes you think you need an approach like this by the way? It's almost never needed IME, usually even a WHERE is preferable for dynamic code for simplicity.

Re: Need to create a macro variable from a dataset

PaigeMiller — Tue, 28 Jul 2020 15:58:18 GMT

@ambadi007 wrote:

it worrked which has data ... but for getting cat1=0 that time it is not working

There are many solutions provided. Which one are you talking about? Why don't you try them all?