Macro for variable lists

[Mgarret]

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)

[Tom]

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;

[Mgarret]

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.

[Tom]

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;

[Tom]

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)