topic create a macro which will hold the suffix of second column name in SAS Programming

create a macro which will hold the suffix of second column name

subrat1 — Tue, 20 Nov 2018 14:35:26 GMT

I have table as shown below:

Sourcetable	Count_103018	Count_110518	Count_111218	Count_111918
CRS	19,869	19,924	20,270	20,233
CRS-Rater	16,429	16,569	16,893	17,125
InRaterOnly	18,662	18,702	18,691	19,235
TOTAL	54,960	55,195	55,854	56,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)

Re: create a macro which will hold the suffix of second column name

Tom — Tue, 20 Nov 2018 14:45:04 GMT

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

Re: create a macro which will hold the suffix of second column name

Cynthia_sas — Tue, 20 Nov 2018 14:49:53 GMT

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

Re: create a macro which will hold the suffix of second column name

subrat1 — Tue, 20 Nov 2018 14:50:10 GMT

table as SAS dataset

Re: create a macro which will hold the suffix of second column name

SuryaKiran — Tue, 20 Nov 2018 14:50:25 GMT

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;

Re: create a macro which will hold the suffix of second column name

r_behata — Tue, 20 Nov 2018 14:57:21 GMT

%let dsid=%sysfunc(open(dataset_name));
%let var_nm=%scan(%sysfunc(varname(&dsid,2)),2,'_');
%let dsid=%sysfunc(close(&dsid));
%put &=var_nm;

Re: create a macro which will hold the suffix of second column name

Tom — Tue, 20 Nov 2018 14:58:53 GMT

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;