DATA Step, Macro, Functions and more

Variable values to macro variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Variable values to macro variables

Hello All,

I've written the following script that outputs a dataset named labels with:

MarED16

FebED16

JanED16

DecED15

NovED15

and so on ...

 

My code is taking the top 14 "Statp" values listed in the oracle table in YYYYMM format and outputting this desired label. It works well, but my ultimate goal is to get it from this table to do

 

%Let m1=first value in labels

%Let m2=second value in labels

%Let m3=third value in labels

all the way to 

%Let m14=fourteenth value in labels

 

I thought I could do this with proc transpose of the labels dataset but all that gives me is Columns1-Columns14 with no data.

 

I'd appreciate some assistance.  I don't care if proc transposed is used or not.

 

proc format ;  
 value mn_name 01='Jan'
               02='Feb'
               03='Mar'
               04='Apr'
               05='May'
               06='Jun'
               07='Jul'
               08='Aug'
               09='Sep'
              10='Oct'
              11='Nov'
              12='Dec';
		   run;
data months;

set dates;
format abrev mn_name.;
abrev=substr(statp,5,2);
/*drop statp;*/
run;

data labels;
set months;
format abrev mn_name.;
labels=cat(put(abrev,mn_name.),"ED",substr(statp,3,2));
drop statp abrev;
run;
proc transpose data=labels
out=labelsarray;
run;
data m;
set labels;
array m[*] m1-m14;
do i=1 to 14;
m[i]=labels;
end;
run;


 


Accepted Solutions
Solution
‎05-24-2016 03:47 PM
Super User
Posts: 5,071

Re: Variable values to macro variables

If I understand correctly (unfortunately that's a big IF in this case), you could add a single statement to your DATA LABELS step.  After computing LABELS=, insert:

 

call symputx( 'm' || left(put(_n_, 2.)), labels);

 

You might not need the PROC TRANSPOSE and the final DATA step.  See if that creates all the macro variables you want, in the proper order.

View solution in original post


All Replies
Super User
Posts: 17,748

Re: Variable values to macro variables

What are you trying to do?

 

Add labels onto a variable or create a variable that has labels?

 

If its add labels and you're using proc transpose look into ID and IDLABEL statement which are very helpful and may help you avoid the macro variables.

Occasional Contributor
Posts: 15

Re: Variable values to macro variables

I'm trying to take a data set named labels and assigns its values to macro variables.  

 

%Let m1=first observation in "labels" dataset.

....

%Let m14=last observation in "labels" dataset.

Super User
Super User
Posts: 6,495

Re: Variable values to macro variables

Without an example input it is hard to tell what your code does. At first look it appears to have way too many separate steps. You can do more than one calculation at a time.

 

But if you already have a dataset named LABELS with a variable named LABEL that contains the values you want to assign to macro variables M1 to Mn, where n is the number of observations in the dataset LABELS then you just need this simple data step.

 

data _null_;
  set labels;
  call symputx(cats('M',_n_),label);
run;

The bigger question is why do you want to create so many macro variables?  Especially when you are creating them from values that you already have in data.

Occasional Contributor
Posts: 15

Re: Variable values to macro variables

 

Because I'm trying to create the labels automatically for my HUGETABLE that is my begginner sas attempt 
at using proc sql to transpose 12 months worth of data from a single table into columns for each month,
joined with data from about 10 other tables. It's obviously not the most efficient script but it works.
In this final table bellow I use the macro variables created manual %Let m1=MarED16 for example
to rename the variables in this Huge Table from ED1-ED13 to the associated month with that ED columnn
such as MarED16 in the final report.

If you have a better way of doing this I'm all ears!





proc sql; create table HugeTable as Select /*distinct*/ s.Company,s.site,a.ID,q._loc as loc,q.STATE,q.STATE_MOD,q.CITY,q.SFCTY, substr(a.item,1,2) as Type,a.ITEM, a.DATAFLAG,a.rpdata, a.eddata, ed2 as &m2, ed3 as &m3,ed4 as &m4,ed5 as &m5,ed6 as &m6,ed7 as &m7,ed8 as &m8,ed9 as &m9,ed10 as &m10,ed11 as &m11,ed12 as &m12, ed13 as &m13, /*comment out ed14 if todays month is 1 month ahead of statperiod*/ /*ed14 as &m14,*/ round(abs(a.EDDATA-&m2)) as DiffCurr_Prior,o.MFWP as MFW_Pub, round(abs((a.EDDATA-&m2)/(a.EDDATA+&m2)*100)) as PrcntChngCM_from_PM, round(min(&stat)) as Min, round(max(&stat)) as Max, round(median(&stat)) as Median, round(mean(&stat)) as Avg, round((EDDATA-mean(&stat))) as DiffEddata_Avg, (EDDATA-o.MFWP) as MFWDiff, case when EDDATA=ED2 then "Yes" end as Dupe, p.errors, /*783 items up to this point*/ q._Who as Analyst, r.new_text as item_note, v.new_text as form_note from base a left join Pub o on a.ID=o.id and a.item=o.prod left join flags p on A.ID=p.id and a.item=p.item left join Who q on a.id=q.id left join itemnotesconcat r on a.id=r.id and a.item=r.item_steps left join formnotesconcat v on a.id=v.id left join Company s on a.id=s.id Where a.EDDATA not in (.,0) or ed3 not in (.,0) or ed4 not in (.,0) or ed5 not in (.,0) or ed6 not in (.,0) or ed7 not in (.,0) or ed8 not in (.,0) or ed9 not in (.,0) or ed10 not in (.,0) or ed11 not in (.,0) or ed12 not in (.,0) or ed13 not in (.,0) or /*If we've rolled over and today's date is only one month ahead of the current stat period than uncomment the line below*/ /**/ ed2 not in (.,0);

 

Solution
‎05-24-2016 03:47 PM
Super User
Posts: 5,071

Re: Variable values to macro variables

If I understand correctly (unfortunately that's a big IF in this case), you could add a single statement to your DATA LABELS step.  After computing LABELS=, insert:

 

call symputx( 'm' || left(put(_n_, 2.)), labels);

 

You might not need the PROC TRANSPOSE and the final DATA step.  See if that creates all the macro variables you want, in the proper order.

Occasional Contributor
Posts: 15

Re: Variable values to macro variables

data labels;
set months;
format abrev mn_name.;
labels=cat(put(abrev,mn_name.),"ED",substr(statp,3,2));
call symputx( 'm' || left(put(_n_, 2.)), labels);
drop statp abrev;
run;

This seemed to create the macro variables I needed.  Thank you!!!!!!!!! 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 405 views
  • 0 likes
  • 4 in conversation