Help using Base SAS procedures

Macro for variable lists

Reply
Frequent Contributor
Posts: 142

Macro for variable lists

Hi all--

I have to organize a series of datasets which are eventually outputted to xml spreadsheets for reporting purposes. In the datasets, there are a series of variables that startwith ChildAge Physical Education Emotional and Social. These variables repeat depending on how many cases are evaluated so if 5 cases were evaluated the number of  “Physical” variables would be 5 and the variables would look like this Physical1 Physical2 Physical3 Physical4 Physical5. The number of these variables could be different for each dataset. To take the variation into account when forming the final datasets, I wrote a macro.

The issue I’m having is if a specify “9” (or any number) as the parameter, I will only get back up through 8 variables when there is 9 in the dataset or if the parameter is 5 the return will be 4 BUT sometimes for just 1 variable set I will get back all the variables. I used the mprint option to try to see what the issue and the log does show that the correct number of variables are being brought in but when I look at the dataset the last ones are normally missing.

I can’t figure out what the issue is –any help is greatly appreciated.   

Thank you.

optionsmprint;

%macro test(final);

proc sql;

create table WANT as

Select

Quest "CaseReview #",

Program_Name "Program Name",

%doi = 1% to &final;

%if &i ne &final %then %do;

ChildAge&i "Child #&i Age",

%end;

%else %do; ChildAge&i "Child #&i Age"

%end;

%end;

"-------->" as d2 "Component 1 ",

%do i = 1% to &final;

%if &i ne &final %then %do;

Physical&i "Child #&i Physical Development",

%end;

%else %do; Physical&i "Child #&i Physical Development",

%end;

%end;

"-------->"as d3 "Sub-Component 2: Cognitive ",

%doi = 1% to &final;

%if &i ne &final %then %do;

Education&i "Child #&i Cognitive ",

%end;

%else %do; Education&i "Child #&i Cognitive "

%end;

%end;

"-------->"as d4 "Sub-Component 3: Psychological",

%doi = 1%to &final;

%if &i ne &final %then %do;

Emotional&i "Child #&i Psychological",

%end;

%else %do; Emotional&i "Child #&i Psychological "

%end;

%end;

"-------->"as d5 "Sub-Component 4: BehavioralAssessment"

%doi = 1 %to &final;

%if &i ne &final %then %do;

Social&i "Child #&i Behavioral Assessment",

%end;

%else%do; Social&i "Child #&i Behavioral "

%end;

%end;

"-------->"as d6 "Sub-Component 5: Source ofAssessment",

Q33,

Q34,

Q35

From HAVE;

%mend test;

%test(9)

Super User
Super User
Posts: 6,498

Re: Macro for variable lists

Seem to me this is much easier to do in regular SAS than trying to use MACRO or SQL.

data want ;

   set have ;

   keep Quest Program_Name ChildAge: Physical: Education: Emotional: Social: ;

run;

Frequent Contributor
Posts: 142

Macro for variable lists

I tried that but I don't get the result im looking for. what I specify Physical: a get a return this other variables I don't want like Physicalong. I am also using lists from dictionary tables so I need to use SQL. Thanks for your input.

Super User
Super User
Posts: 6,498

Re: Macro for variable lists

Then use lists.

%let final=9;

data want ;

   set have ;

   keep Quest Program_Name

     ChildAge1-ChildAge&final

     Physical1-Physical&final

     Education1-Education&final

     Emotional1-Emotional&final

     Social1-Social&final

  ;

run;

Super User
Super User
Posts: 6,498

Re: Macro for variable lists

What is the purpose of the query?  Is it just make a copy of the data with fewer columns?

Why do your datasets have multiple columns instead of multiple rows when there are repititions?

Why do you need to add labels in your SELECT statement? Don' t the variables already have labels in the source dataset?

I would assume the all empty columns are because you tried to pull column ChildAge5 and it did not exist in the source dataset.

If you are generating the select statement with macro code it will be easier if just have one loop.  It will change the order of the variables however.

You do not need any special logic for eliminating comma as you have variables before and after the values generated by the macro logic.

Also I find that long SQL select lists are much easier to read if you place the commas at the beginning of the line instead of the end.  They also make a much more pleasing pattern.

%macro test(final);

proc sql;

  create table WANT as

    select

      Quest "CaseReview #"

     ,Program_Name "Program Name"

%do i = 1 %to &final;

     ,ChildAge&i "Child #&i Age"

     ,Physical&i "Child #&i Physical Development"

     ,Education&i "Child #&i Cognitive "

     ,Emotional&i "Child #&i Psychological"

     ,Social&i "Child #&i Behavioral Assessment"

%end;

     ,"-------->" as d6 "Sub-Component 5: Source ofAssessment"

     ,Q33

     ,Q34

     ,Q35

  from HAVE

;

quit;

%mend test;

%test(9)

Ask a Question
Discussion stats
  • 4 replies
  • 147 views
  • 0 likes
  • 2 in conversation