Need to create a macro variable from a dataset

[ambadi007]

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

[Reeza]

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

---

[ambadi007]

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

---

---

[Reeza · Accepted solution]

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

[Kurt_Bremser]

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

[PaigeMiller]

---

@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?

[Astounding]

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?

[ambadi007]

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 

[PaigeMiller]

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?

[ambadi007]

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

[PaigeMiller]

---

@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?

[Tom]

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;