BookmarkSubscribeRSS Feed
Banu
Obsidian | Level 7
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.
8 REPLIES 8
art297
Opal | Level 21

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

 

ArtC
Rhodochrosite | Level 12

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.

Banu
Obsidian | Level 7

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 Dataset:(Template-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 structure:(structure 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.

BrunoMueller
SAS Super FREQ

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

Banu
Obsidian | Level 7
Thanks for your suggestion. But as per my details, Final dataset structure is Fixed.it won't change, only detail dataset data will vary.
BrunoMueller
SAS Super FREQ

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

Banu
Obsidian | Level 7

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

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1630 views
  • 0 likes
  • 5 in conversation