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

I have table as shown below:

 

SourcetableCount_103018Count_110518Count_111218Count_111918
CRS19,86919,92420,27020,233
CRS-Rater16,42916,56916,89317,125
InRaterOnly18,66218,70218,69119,235
TOTAL54,96055,19555,85456,593

 

I want to create a macro which will hold the suffix of second column name. Like for example the second column name here is Count_103018, So macro should hold 103018, (second column name will change as per the week)

1 ACCEPTED SOLUTION

Accepted Solutions
r_behata
Barite | Level 11
%let dsid=%sysfunc(open(dataset_name));
%let var_nm=%scan(%sysfunc(varname(&dsid,2)),2,'_');
%let dsid=%sysfunc(close(&dsid));
%put &=var_nm;

 

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

Do you have that "table" as a SAS dataset or a text file or some other format?

subrat1
Fluorite | Level 6
table as SAS dataset
Cynthia_sas
SAS Super FREQ
Hi:
You are not providing enough information:
1) WHAT do you want to create macro VARIABLE, macro PROGRAM, use the value in a macro FUNCTION? Why do you need a "macro"?
2) WHY do you need the suffix of the column name? How are you going to use this value? Can you provide an example? What subsequent processing is going to be performed. You show 4 variables with numeric suffixes. Do you need to get the number from all 4 columns or only of the second column?
3) Your explanation implied that the second column name will change per week -- but you don't explain why this is significant? What happens to the column each week? Why does it change? Again, why do you need the suffix from only the second week?
4) What kind of process do you envision creating this new macro "something" with -- DATA step? SQL? simple %LET statements? Knowing What you're creating and how it's going to be used and having a picture of the entire process will help here.
5) What does your data file look like. Is the above your report output or your starting data table? Is this data already in SAS format or does it need to be read into SAS format? What kind of processing are you doing with the data. It is a rare data table that holds a TOTAL row. This looks like a report. Where does the report come from? How do you get this report/table? The structure of the data or file will impact the solution.
6) What code have you tried?

I'm hoping that these questions will allow you to define your needs in such a way that someone can make a constructive comment on your question.

Cynthia
SuryaKiran
Meteorite | Level 14

You can query the dictionary.columns for the second variable in the table.

 

proc sql;
		select strip(scan(name,2,'_')) into :var_2
			from dictionary.columns
				where libname='WORK'  /* Library name */
					and memname='HAVE'  /* Table name */
					and varnum=2       /* Variable 2 */
	;
quit;

%put &var_2;
Thanks,
Suryakiran
r_behata
Barite | Level 11
%let dsid=%sysfunc(open(dataset_name));
%let var_nm=%scan(%sysfunc(varname(&dsid,2)),2,'_');
%let dsid=%sysfunc(close(&dsid));
%put &=var_nm;

 

Tom
Super User Tom
Super User

Assuming that the suffix on that names is date value in MMDDYY format (who uses two digit years? Y2K anyone?).

Not sure you need any macro variables.  For example you could convert that structure to a TALL structure where the date value is actually in a variable instead of trapped in the variable's name.

data want ;
   set have ;
  array _count count_: ;
  do month_no=1 to dim(_count);
     count=_count(month_no);
     date = input(scan(vname(_count(month_no)),2,'_'),mmddyy8.);
     output;
  end;
  format date yymmdd10. ;
  drop count_: ;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 807 views
  • 5 likes
  • 5 in conversation