Macro check variable exists and generate new dataset

Reply
Occasional Contributor
Posts: 16

Macro check variable exists and generate new dataset

Hi,

 

I am trying to check some variable with same prefix existing or not. If so, generate some other variable values. If not check the next one.

 

variables start with "_" with a number. For example _25,_50. The interval is 25 but not all are existing. Some are missing. The code I wrote will generate all the variable even it is not exist. Can't find out the problem. Could anyone have a look?

 

Thanks,

Andrea

 

The code:

 

%macro test();

data test;
set test_desc5 ;
drop _name_ _label_;

dsid=open('test_desc5');

%do i=25 %to 1500 %by 25;

if varnum(dsid,'_&i.')^=0 then do;


if _&i = '' then
_&i = '';

if A&i = '' then
A&i = .;

end;


%end;

rc=close(dsid);

drop rc dsid;
run;


%mend;

 

%test;

 

The data like:

_25               _50               _100 .            .....A0       A25    A100 ......

0.7(0.4,1.5)  0.8(0.5,1.5)  0.9(0.6,1.6)    ......10       20      30.........

0.6(0.3,1.4)  0.7(0.4,1.4)  0.8(0.5,1.5)    ......20       30      40.........

.

.

.

Trusted Advisor
Posts: 1,683

Re: Macro check variable exists and generate new dataset

Posted in reply to Andrea_Peng

In order to resolve the macro variable it should be enclosed by double quote:

if varnum(dsid,"_&i.")^=0 then do;
Occasional Contributor
Posts: 16

Re: Macro check variable exists and generate new dataset

Thanks to point out the problem!

Super User
Super User
Posts: 8,634

Re: Macro check variable exists and generate new dataset

Posted in reply to Andrea_Peng

Thats really not a good approach.  Firstly do you really have a dataset with 60 variables with _ prefix?  Secondly why are you doing this with macro?  This is simple datastep process.  For instance:

data template;
  do v=25 to 1500 by 25;
    output;
  end;
run;
proc transpose data=template out=t prefix=_;
  var v;
  id v;
run;
data want;
  set t (obs=0) sashelp.class;
run;

The above shows creating the dataset as you want it to look like, then setting that with your data (replace the class one with your dataset obviously).  However you have these problems because you are working with a transposed dataset.  Have your data items going down the page, e.g:

TIMEPOINT  RESULT
25                  xyz
50                  def
...

And you will find your programming a lot easier - at the end when it goes for a report then you can transpose it.

 

Occasional Contributor
Posts: 16

Re: Macro check variable exists and generate new dataset

It is easier to use data step but this is just a small part of a large macro. I have to put it into a macro.

Super User
Super User
Posts: 8,634

Re: Macro check variable exists and generate new dataset

Posted in reply to Andrea_Peng

"small part of a large macro" that there is probably a bad process to start with.  Look at your documentation, the functional design, see where code can be compartmentalised, then create macros for those parts, use Base SAS for all else.  Macro is not a replacement for Base SAS.  

PROC Star
Posts: 169

Re: Macro check variable exists and generate new dataset

[ Edited ]
Posted in reply to Andrea_Peng

@Andrea_Peng:

Apart from the fact that you may have a problem with single quotes rather than double quotes in varnum(dsid,'_&i.'), the approach is not the right one - what you are doing is to generate code for all variables, whether they exist or not. If you want generate code only for the variables that actually exist, you should open the dataset ID in the macro, not in the datastep, something like:

%macro test();
%local dsid rc i;
%let dsid=%sysfunc(open(test_desc5));

data test;
set test_desc5 ;
drop _name_ _label_;
%do i=25 %to 1500 %by 25;
  %if %sysfunc(varnum(&dsid,_&i.))^=0 %then %do;
    /* Code to execute if the variable _&i exists: */
    if _&i = '' then /* something wrong here, what are you trying to do?? */
      _&i = '';
    if A&i = '' then
      A&i = .;
    %end;
  %end;
%let rc=%sysfunc(close(&dsid));
run;

%mend;
Ask a Question
Discussion stats
  • 6 replies
  • 141 views
  • 0 likes
  • 4 in conversation