Get a value from a table and set it to be a numeric number for later reference.

[phongpham]

Hi mates, 

I am new SAS user. I am trying to get a value from a table and allocate it into a variable for later using but don't know how to do it. Just wonder if you guys can help me with that. For example. Here I would like to get N_miss value (=9) from table N_miss and allocate this value to a variable called n_extent. Thanks for your help!

[phongpham]

FYI, I will need reference for a loop like this:

[ballardw]

Please place code in either a text box, opened with the </> icon, or a code box, opened with the "running man" icon instead of pictures. With text we can edit your text or copy and paste into an editor and run or modify the code to create a working solution. Not so much with pictures and as the day goes on I am much less likely to completely retype code.

Next, what does your data set Delta or N_miss look like? And with those values in mind, what value do you expect to place into that do loop?

Almost certainly the solution will involve CALL SYMPUTX but if your data set Delta has more than one record which one do you want?

Also, it is poor technique to just have a macro variable appear in the middle of code. It is better practice to make such things parameters for the macro so some one with access to the macro code knows where the value comes from.

And last, if the only thing you use that macro for is to combine data sets with numbers you do not need a macro loop at all.

Data delta_final;
   set delta1 - delta9;
run;

for example uses a data set list and will combine delta1, delta2, delta3 , ...,delta9.

So if you have macro variable with the number, which we go back to that question about what is in the N_miss data set, you would use

Data delta_final;
   set delta1 - delta&n_miss. ;
run;

[phongpham]

Hi mate,

Thanks for your quick reply and sorry for not put the code on the code tag. Just the first time I post here, so did not pay attention to it.

I attach here Delta file and the code I produced for your reference. 

Cheers,

%macro loop (n=);
data delta_&n.;
set delta;
N_quarter =  %eval(&n.) + N_quarter;
gdp_base1 = gdp_base1 + delta_GDP * %eval(&n.);
RTO_base1 = RTO_base1 + delta_RTO * %eval(&n.);
ICR_base1 = ICR_base1 + delta_ICR * %eval(&n.);
BCG_base1 = BCG_base1 + delta_BCG * %eval(&n.);
run;
%mend;

data N_miss (keep=N_miss);
set delta;
run;

/* %let n_miss = 9; */


%macro extrapolate_forecast;
%do i = 1 %to 9; /* need to think how to add 9 here from N_miss */
	%loop (n=&i.);
%end;

data delta_final;
 set %do j = 1 %to 9;
	delta_&j.
	%end;
;
run;
%mend;

%extrapolate_forecast;

[Reeza · Accepted solution]

%macro loop (n=);
data delta_&n.;
set delta;
N_quarter =  &n. + N_quarter;
gdp_base1 = gdp_base1 + delta_GDP *&n.;
RTO_base1 = RTO_base1 + delta_RTO *&n.;
ICR_base1 = ICR_base1 + delta_ICR *&n.;
BCG_base1 = BCG_base1 + delta_BCG *&n.;
run;
%mend;


%macro extrapolate_forecast(n_miss=);
%do i = 1 %to &n_miss; /* need to think how to add 9 here from N_miss */
	%loop (n=&i.);
%end;

data delta_final;
 set %do j = 1 %to 9;
	delta_&j.
	%end;
;
run;
%mend;

data N_miss (keep=N_miss);
set delta;
call execute(catt('%extrapolate_forecast(n_miss = ', 
                     n_miss, 
                     ');')
                    )
                      
run;

That being said, I think using shortcut references as @ballardw suggest is more efficient. If you have unique prefixes this becomes trivial. 

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

And I highly suggest using CALL EXECUTE for macro looping/calling instead of macro loops. It makes things much cleaner, easier to debug and maintain which are huge wins IMO. Looking closely at your code though, I really do wonder if arrays are not the correct answer here instead....but it's your program 🙂

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[phongpham]

Hi Reeza,

Thanks for your solution!

It works for me. 🙂

As I mentioned in my post, I am new to SAS, so there are tons of things to learn.

Very much appreciate your help and your resource.

Cheers,

Phong Pham

[Tom]

I don't understand what data you have or what you are trying to do.

Please explain what you are trying to do first.  Before showing how you are trying to do it.

If you want make 9 copies of the same data why use macro loops or even multiple data steps?

data exploded;
  set delta;
  do offset = 1 to 9;
     New_n_quarter = offset + n_quarter;
     New_gdp_base1 = gdp_base1 + delta_GDP * offset ;
     New_RTO_base1 = RTO_base1 + delta_RTO * offset ;
     New_ICR_base1 = ICR_base1 + delta_ICR * offset ;
     New_BCG_base1 = BCG_base1 + delta_BCG * offset ;
     output;
  end;
run;

As to subject line. I assume by table you mean a SAS dataset and not a table you want to in your written report.

What does the dataset have in it?  What do you want to use that information for?

What do you mean by a "numeric number"?  

In general if you want to use a value from a dataset then include that dataset into your calculations.  For example you created a dataset with the MEAN and STD of a variable you could re-combine it with the original dataset to calculate a ZSCORE like this.

data want;
   if n=1 then set mean_std;
   set have;
   zscore = (var- mean) / std ;
run;
   

[phongpham]

Thanks @Tom  for your help and questions!

Yes, by table I mean a SAS dataset.

- The information in the table is as below

- By the numeric number here, I mean number 9 in the N_miss columns.

- I want to put that number of N_miss (9) into line 3 like this " do offset = 1 to N_miss". Since this months, N_miss is 9, I am temporarily put it =9, but next month, the number can change, so I would like to automate it.

-  I not quite understand what do you want to say in your example. Do you mean merging dataset, but here I want to get number from dataset and set this number as a reference to stop a loop. 

data exploded;
  set delta;
  do offset = 1 to 9;
     New_n_quarter = offset + n_quarter;
     New_gdp_base1 = gdp_base1 + delta_GDP * offset ;
     New_RTO_base1 = RTO_base1 + delta_RTO * offset ;
     New_ICR_base1 = ICR_base1 + delta_ICR * offset ;
     New_BCG_base1 = BCG_base1 + delta_BCG * offset ;
     output;
  end;
run;

[Tom]

If you have a variable name N_MISS that is already in the dataset then why make things complicated?

Just reference the variable N_MISS in the DO loop.

data want;
  set delta;
  do offset=1 to n_miss;
     ....
  end;
run;

[phongpham]

Thanks Tom. Very much appreciate your help! 🙂