macro and do loop

[TX_STAR]

Hello members,

I created macro variable (CAT1) from proc sql select into. And want to use it in the data step in the do loop: &SUB._CAT[J]=&&CAT&J.;

It does not work. Where did I do wrong? Thanks!

-----------------

proc sql;
select rc into :CAT1 from rc2 where Objective_1="&subj." and Question_No='11';
quit;
%put RC1 = &CAT1;

data tpsalt3;
   set tpsalt2;

   array &SUB._CAT {&ITEM_TOT.} $ &SUB._CAT1-&SUB._CAT&ITEM_TOT.;

   do J=1 to &ITEM_TOT.;
      &SUB._CAT[J]=&&CAT&J.;
   end;

drop J;
run;

[PaigeMiller]

I think you have created a rather difficult to understand combination of macros and arrays and it would be helpful if you explained what you are doing in words.

Next, you need to create working valid SAS code WITHOUT macros and WITHOUT macro variables for one or two cases. If you don't have working valid SAS code without macros and without macro variables, you will never get it to work with macros. Show us the working code without macro language.

Lastly, I doubt that macros are needed here, I simply don't see why you don't use arrays and stop there.

[ScottBass]

Where did I go wrong?"

Lack of understanding when macro code gets executed, and incorrect intermixing of macro and data step statements. 

In summary, (in general) macro is just text substitution before the data step even starts executing.

---

proc sql;
select rc into :CAT1 from rc2 where Objective_1="&subj." and Question_No='11';
quit;
%put RC1 = &CAT1;  <<< all this would do is cause confusion.

Post the value of &CAT.  And does the where clause guarantee a single row is returned?  If not, you probably want separated by ' ' in your SQL statement.

%put &=cat1;

data tpsalt3;
   set tpsalt2;

   array &SUB._CAT {&ITEM_TOT.} $ &SUB._CAT1-&SUB._CAT&ITEM_TOT.;

   do J=1 to &ITEM_TOT.;
      &SUB._CAT[J]=&&CAT&J.;
   end;

drop J;
run;

---

Do you really have an unknown number of columns in your source dataset?

I suspect your data step could be written without macro at all:

data tpsalt3;
   set tpsalt2;

   array ary{*} myvar:;

   do J=1 to dim(ary);
      &SUB._CAT[J]=&&CAT&J.;  * <<< my guess is this line will never work! ;
      ary{j}=???;  * <<< what are you trying to do here? ;
   end;

drop J; 
run;

Otherwise, explain what you're trying to do, and see if you can do it without macro, which i suspect you can.

[Kurt_Bremser]

You seem to want to use a series of macro variables (indicated by the indirect reference &&CAT&J), but create only CAT1. That won't work in the first place.

I am about 100% confident you don't need any macro programming here at all. Please post example data for tpsalt2 and rc2 in data steps with datalines, and what you want to get out of it in tpsalt3.

Please also provide what is contained in macro variables SUB and SUBJ.

Something like this:

%put RC1 = &CAT1;

is also a VERY BAD idea, as it will mislead anyone (including you!) looking at the log.

[gamotte]

I think what you want is :

&SUB._CAT[j]=symget(cats('CAT', J));

%let cat1=x;
%let cat2=y;
%let cat3=z;

data have;
array c $ c1-c3;
do j=1 to 3;
    c(j)=symget(cats('cat',j));
end;
run;

But as @Kurt_Bremser has pointed, there is probably a better non macro solution to your problem.

[gamotte]

Also, as already said, you only create a macrovariable &CAT1. though it seems you

want to loop on several values.

Perhaps you want something as follows :

data cat;
input cat $;
cards;
X
y
z
;
run;

proc sql;
SELECT cat INTO :cat1-:cat3
FROM cat;
quit;

[TX_STAR]

I am sorry for confusing question.

I have item category variable rc in data rc2. Using the objective_1 and Question_No variables, I can subset the item category info for 10 items. 

I have student responses to 10 items (10 variables) in data tpsalt3. 

I want to abstract the item category information (in rc variable in rc2 dataset) and converted to 10 item category variable (cat1....cat10) and merge to tpsalt3 so that I have item response and item category variables all in one data set. 

In the code that I posted, I wanted to abstract item category information from variable rc in dataset rc2 using marco variables. To same space, I only posted one proc sql; select into;  instead of 10. (misleading information #1).

The I wanted to create 10 item category variables (&sub._cat1 - &sub._cat10) using loop in data step. I mixed macro with data step loop (misleading information #2).

I think, I can directly create 10 item category variables from rc2 by subset and transpose variable rc then directly merge to tpsalts. 

Which way is better for this purpose?

thanks a lot for your responses!

[Kurt_Bremser]

Please provide example data in a data step with datalines, and the expected result.

[TX_STAR]

data rc2

Obs	Question_No	rc
1	11	1
2	12	1
3	13	1
4	14	1
5	15	1
6	16	2
7	17	2
8	18	1
9	19	1
10	20	2

data tpsalt3

ID	S_ITEM1	S_ITEM2	S_ITEM3	S_ITEM4	S_ITEM5	S_ITEM6	S_ITEM7	S_ITEM8	S_ITEM9	S_ITEM10

data want;

the S_CAT1 - S_CAT10 will have values from variable rc in data rc2: 1111122112 for every student. S_ITEM1 -S_ITEM10 have student responses. 

ID	S_ITEM1	S_ITEM2	S_ITEM3	S_ITEM4	S_ITEM5	S_ITEM6	S_ITEM7	S_ITEM8	S_ITEM9	S_ITEM10	S_CAT1	S_CAT2	S_CAT3	S_CAT4	S_CAT5	S_CAT6	S_CAT7	S_CAT8	S_CAT9	S_CAT10

[Tom]

Just use PROC TRANSPOSE to convert your tall/skinny data into short/wide data.

proc transpose data=rc2 out=want prefix=S_ITEM;
  id question_no;
  var rc;
run;

Your real data probably has groups of values for each participant.  So you will probably want to include the variable(s) that identify each group in a BY statement.

[TX_STAR]

The transposed item category data has 10 variables, one record, no ID variable. How to do the many to one merge so that in the new data set every student record has 10 populated item category variables (S_CAT1-S_CAT10). When use merge, only the first record has item category values. The 10 variables (S_CAT1-S_CAT10) are empty for all records other than the first one.  

[Tom · Accepted solution]

Show what code you used as most methods will retain the values from the "short" dataset.

If you know that your dataset just has one observations then write your data step to read it just once.

data want ;
  set have ;
  if _n_=1 then set s_items;
run;

Just make sure that the variable S_ITEMS are not also in HAVE because if they are then when you read the second value from HAVE it will overwrite whatever was read from S_ITEMS.

[TX_STAR]

have only has one record (and 10 variables s_cat1 - s_cat10). s_items have student responses (many records). dataset Have does not have s_items variable.
I used
data want;
merge have s_items;
run;

I didn't work. The want data set has all variables but the first record of s_cat1 - s_cat10 have values. The rest are empty.
Why use if _n_=1 then set s_items;

[Tom]

I would use the IF _N_=1 because then the code shows what you are doing. Most SAS programmers would consider a merge statement without a BY statement as an indication of a coding error because of the missing BY statement.  And without the BY statement SAS does not retain the values from the short dataset.

But either way you cannot the variables on both input datasets. 

Examine the output for the BOTH and RIGHT variables from the TEST1 and TEST2 datasets in this example.

data long;
  input id left both ;
cards;
1 1 1
2 2 2
3 3 3
;

data short;
  input both right;
cards;
4 4
;

data test1 ;
  merge long short;
run;

proc print;
run;

data test2 ;
  set long ;
  if _n_=1 then set short;
run;

proc print;
run;

SAS 9.4 on WINDOWS

Obs    id    left    both    right

 1      1      1       4       4
 2      2      2       2       .
 3      3      3       3       .

SAS 9.4 on WINDOWS

Obs    id    left    both    right

 1      1      1       4       4
 2      2      2       2       4
 3      3      3       3       4

[TX_STAR]

Thanks so much for the explanation!