DATA Step, Macro, Functions and more

Validating Macro variables values in SUM expression

Reply
Contributor
Posts: 31

Validating Macro variables values in SUM expression

Hi,

I need help to find macro variable has value or null.
Below is my code.

Data a;
Input name $ amount;
Datalines;
Canada1 12.30
Canada2 45.90
Canada .
France1 35.50
France2 55.50
France .
;
Run;

Data _null_;
Set a;
If name='Canada1' then do;
Call Symput('x1',name);
Call Symput('y1',amount);
End;
If name='Canada2' then do;
Call Symput('x2',name);
Call Symput('y2',amount);
End;
If name='France1' then do;
Call Symput('m1',name);
Call Symput('n1',amount);
End;
Run;
If name='France2' then do;
Call Symput('m2',name);
Call Symput('n2',amount);
End;

Data b;
Set a;
If name= 'Canada' then do;
Total_amunt=%sysevalf(&y1,&y2);
End;
Else if name='France' then do;
Total_amunt=%sysevalf(&'n1',&'n2');
End=;

In the above data step, &n1,&n2,&y1,&y2 values
Would contains either values or null values.
How can I manage if above macro variables have null values. I needs to sum all these numeric macro variables sum with in IF condition.

Please help me.
PROC Star
Posts: 7,357

Re: Validating Macro variables values in SUM expression

Difficult to address what you say your problem is, as your code has a number of mistakes AND, while you want sums, you don't include the sum function. The following does what your code looks like it should be doing. The sum function ignores missing values:

 

Data a;
Input name $ amount;
Datalines;
Canada1 12.30
Canada2 45.90
Canada .
France1 35.50
France2 55.50
France .
;
Run;

Data _null_;
Set a;
If name='Canada1' then do;
Call Symput('x1',name);
Call Symput('y1',amount);
End;
If name='Canada2' then do;
Call Symput('x2',name);
Call Symput('y2',amount);
End;
If name='France1' then do;
Call Symput('m1',name);
Call Symput('n1',amount);
End;
If name='France2' then do;
Call Symput('m2',name);
Call Symput('n2',amount);
End;
Run;

Data b;
Set a;
If name= 'Canada' then do;
Total_amunt=sum(&y1,&y2);
End;
Else if name='France' then do;
Total_amunt=sum(&n1,&n2);
End;
run;

Art, CEO, AnalystFinder.com

 

Valued Guide
Posts: 632

Re: Validating Macro variables values in SUM expression

I am not sure if you are using macro variables because they are needed for a larger problem or because you believe that they are needed here.  If the objective is to 'calculate the total' then they are not needed (and would be impractical if there are many more rows per country).  The following approach calculates a total, assuming the last line (amount = .) for each country is the line to receive the total.

Data have;
Input name $ amount;
Datalines;
Canada1 12.30
Canada2 45.90
Canada .
France1 35.50
France2 55.50
France .
Run;
data want(keep=name amount);
   set have;
   total+amount;
   if amount=. then do;
      amount=total;
      output want;
      total=0;
   end;
   run;

In this variation no macro variables are used and the final data set only has the country totals.

Contributor
Posts: 31

Re: Validating Macro variables values in SUM expression

Thanks for your suggestions.

I am elaborating my question in detail level. I have 2 datasets,Names are final and detail.

Here Final dataset structure is fixed but detail table structure varies based on input data. So I needs to update the

Final dataset based on details dataset and if any values not availbale then keep it as NULL in final dataset.

 

FINAL DatasetSmiley SadTemplate-Fixed structure)

 

CountryAmount Code
India_Total  
india_cd1  
india_cd2  
india_cd3  
Canada_Total  
canada_cd1  
canada_cd2  
France_Total  
france_cd1  
france_cd2  
france_cd3  

 

 

Detail dataset structureSmiley Sadstructure is not fixed and below one is example).

 

CountryAmount Code
india_cd1500.11212
india_cd21500.91212
india_cd31000.51212
canada_cd2  
france_cd11000.21215
france_cd2  
france_cd31000.51217

 

Here only for India, code is same 1212 for all categories but for other countries code is different for each category.

Please help me to achieve above one. Thanks in advance.

SAS Super FREQ
Posts: 682

Re: Validating Macro variables values in SUM expression

Hi

 

I assume the number of detail lines in the final datasets may also vary from country to country. I f you want to have the total line before the detail lines, you can use Proc REPORT to calculate this for you. See example below how it could be done.

 

/* prepare some fake data */
Data a;
  Input name $ amount;
  name2 = substr(name, 1, anydigit(name) - 1);
Datalines;
Canada1 12.30
Canada2 45.90
France1 35.50
France2 55.50
India1 .
;

/* 
 * calculate the summary line before the detail lines
 * write ou the report data to a SAS dta set
 */
/*ods select all;*/
proc report data=a out=want ;
  column name2 name amount;

  define name2 / order;
  define name / order;
  define amount / analysis sum format=comma14.2;

  break before name2 / summarize;
run;

/* prepare the data to what we need */
data want2;
  length name $ 32;
  set want;
  if lowcase(_break_) = "name2" then do;
    name = catx("_", "Total", name2);
  end;
  keep name amount;
run;

proc print data=want2;
run;

Bruno

Contributor
Posts: 31

Re: Validating Macro variables values in SUM expression

Thanks for your suggestion. But as per my details, Final dataset structure is Fixed.it won't change, only detail dataset data will vary.
SAS Super FREQ
Posts: 682

Re: Validating Macro variables values in SUM expression

Hi

 

You can still use the technique I described with Proc REPORT, just ensure to only select detail rows which also appear in the final structure, then use a left join to combine the final structure with the result created by Proc REPORT, for the final structure data set you should add some seqnr to the rows, so that you can keep the sorting order of the final structure.

 

Bruno

Contributor
Posts: 31

Re: Validating Macro variables values in SUM expression

Thanks for your sugegstion. I will try out as same. Thanks.

Super User
Posts: 17,776

Re: Validating Macro variables values in SUM expression

The sum function can handle blanks, but not non existing macro variables. 

What happens if you insist on the macro variables existing? Declare them locally or globably, as appropriate is one method to do this. That's probably the quickest solution, IMO.

Ask a Question
Discussion stats
  • 8 replies
  • 189 views
  • 0 likes
  • 5 in conversation