BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aero
Obsidian | Level 7

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;


 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Reeza
Super User

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.

aero
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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.

aero
Obsidian | Level 7

 

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);

 

Astounding
PROC Star

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.

aero
Obsidian | Level 7
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!!!!!!!!! 

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
  • 6 replies
  • 1292 views
  • 0 likes
  • 4 in conversation