topic Re: macro and do loop in SAS Programming

macro and do loop

TX_STAR — Mon, 16 Dec 2019 22:56:04 GMT

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;

Re: macro and do loop

PaigeMiller — Mon, 16 Dec 2019 23:01:20 GMT

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.

Re: macro and do loop

ScottBass — Mon, 16 Dec 2019 23:09:13 GMT

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.

Re: macro and do loop

Kurt_Bremser — Tue, 17 Dec 2019 06:58:57 GMT

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.

Re: macro and do loop

gamotte — Tue, 17 Dec 2019 09:00:45 GMT

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.

Re: macro and do loop

gamotte — Tue, 17 Dec 2019 09:25:20 GMT

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;

Re: macro and do loop

TX_STAR — Tue, 17 Dec 2019 15:16:24 GMT

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!

Re: macro and do loop

Kurt_Bremser — Tue, 17 Dec 2019 15:59:59 GMT

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

Re: macro and do loop

TX_STAR — Tue, 17 Dec 2019 16:18:44 GMT

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

Re: macro and do loop

Tom — Tue, 17 Dec 2019 16:23:19 GMT

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.

Re: macro and do loop

TX_STAR — Tue, 17 Dec 2019 17:04:33 GMT

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.

Re: macro and do loop

Tom — Tue, 17 Dec 2019 17:12:08 GMT

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.

Re: macro and do loop

TX_STAR — Tue, 17 Dec 2019 19:16:07 GMT

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;

Re: macro and do loop

Tom — Tue, 17 Dec 2019 19:38:06 GMT

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

Re: macro and do loop

TX_STAR — Tue, 17 Dec 2019 22:20:19 GMT

Thanks so much for the explanation!

Re: macro and do loop

ScottBass — Wed, 18 Dec 2019 21:12:26 GMT

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

@TX_STAR wrote:

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

I appreciate the additional information, but how is this compliant with what Kurt requested? How can we cut-and-paste this ... "stuff" ... into SAS, run with it, and provide you tested code that works? Do you know how to write a data step using the datalines statement?

Help us help you by learning how to post a good question.

Re: macro and do loop

TX_STAR — Thu, 19 Dec 2019 15:20:31 GMT

Sorry for the unclear message.

Here is the example data:

data rc has one record.

data rc;
   input cat1 - cat10;
   cards;
   1 1 1 1 1 2 2 1 1 1
   ;
run;

data tps has many records (only show 10 here).

data tps;
   input id it1 - it10;
   cards;
   1 1 2 3 1 1 1 3 4 5 4
   2 2 2 3 1 3 1 3 4 5 5
   3 3 2 2 2 3 1 2 4 4 4
   4 3 2 2 4 3 1 2 4 5 4
   5 3 2 2 2 3 1 2 4 4 4
   6 3 2 2 4 3 1 2 4 5 4
   7 3 2 2 2 3 1 2 4 4 4
   8 3 2 2 4 3 1 2 4 5 4
   9 3 2 2 2 3 1 2 4 4 4
  10 3 2 2 4 3 1 2 4 5 4
  ;
run;

data want is the data I need which has as many records as data tps. The values for cat1-cat10 are all the same for all records.

data want;
   input id it1-it10 cat1-cat10;
   cards;
   1 1 2 3 1 1 1 3 4 5 4 1 1 1 1 1 2 2 1 1 1
   2 2 2 3 1 3 1 3 4 5 5 1 1 1 1 1 2 2 1 1 1
   3 3 2 2 2 3 1 2 4 4 4 1 1 1 1 1 2 2 1 1 1
   4 3 2 2 4 3 1 2 4 5 4 1 1 1 1 1 2 2 1 1 1
   5 3 2 2 2 3 1 2 4 4 4 1 1 1 1 1 2 2 1 1 1
   6 3 2 2 4 3 1 2 4 5 4 1 1 1 1 1 2 2 1 1 1
   7 3 2 2 2 3 1 2 4 4 4 1 1 1 1 1 2 2 1 1 1
   8 3 2 2 4 3 1 2 4 5 4 1 1 1 1 1 2 2 1 1 1
   9 3 2 2 2 3 1 2 4 4 4 1 1 1 1 1 2 2 1 1 1
  10 3 2 2 4 3 1 2 4 5 4 1 1 1 1 1 2 2 1 1 1
  ;
run;

Initially, I used proc sql; select into to create 10 macro variables to be used in the next data step to create the cat1-cat10 variables in the want dataset. I mixed the macro variable with do loop which did not work. If I do want to use macro variables created with proc sql to create cat1-cat10 at data step, how should I do?

thanks!

Re: macro and do loop

Tom — Thu, 19 Dec 2019 15:28:47 GMT

Not sure what macro variables would do to help. You can just combine the two datasets.

data want;
  set tps;
  if _n_=1 then set rc;
run;

But what is the overall goal here? Are you going to "grade" the response (TPS) based on whether they agree with the answer key (RC)?

Something like this which will compute SCORE as a count of how many of the 10 pairs of values match.

data want;
  set tps;
  if _n_=1 then set rc;
  array key cat1-cat10;
  array answer it1-it10;
  do index=1 to dim(key);
      score=sum(score,key[index]=answer[index]);
  end;
run;