Automate SAS code for multiple sites

[rsva]

Greetings,

I am working on SAS code to create several dichotomous variables using IF-THEN-ELSE statements. I need to create SAS codes for multiple sites where the questions numbers changes between each site. Snapshot of a data set with standard question number and Q #s for site1 and site2.

Standard Question #	Site1 Question #	Site2 Question #
V1	V12	V4
V2	V2	V7
V3	V6	V3
V4	V9	V1
V5	V5	V9
V6	V4	V10
V7	V8	V6
V8	V9	V8
V9	V1	V13
V10	V2	V11

Below is a sample code that uses the standard question number. The code that I have.

HAVE base code

IF V1=1 THEN VAR1=1; ELSE VAR1=2;

IF V2=2 THEN VAR2=2; ELSE VAR2=2;

IF V3 IN (1,2,3) THEN VAR3=1; ELSE VAR3=2;

IF V4 IN (1,5) THEN VAR4=1; ELSE VAR4=2;

IF V5=1 THEN VAR5=1; ELSE VAR5=2;

IF V6=2 THEN VAR6=2; ELSE VAR6=2;

IF V7 IN (1,5) THEN VAR7=1; ELSE VAR7=2;

IF V8=1 THEN VAR8=1; ELSE VAR8=2;

IF V9=2 THEN VAR9=2; ELSE VAR9=2;

IF V10 IN (1,2,3) THEN VAR10=1; ELSE VAR10=2;

I need to create codes for site1 and site2, below:

Need code for Site1	Need code for Site2
IF V12=1 THEN VAR1=1; ELSE VAR1=2;	IF V4=1 THEN VAR1=1; ELSE VAR1=2;
IF V2=2 THEN VAR2=2; ELSE VAR2=2;	IF V7=2 THEN VAR2=2; ELSE VAR2=2;
IF V6 IN (1,2,3) THEN VAR3=1; ELSE VAR3=2;	IF V3 IN (1,2,3) THEN VAR3=1; ELSE VAR3=2;
IF V9 IN (1,5) THEN VAR4=1; ELSE VAR4=2;	IF V1 IN (1,5) THEN VAR4=1; ELSE VAR4=2;
IF V5=1 THEN VAR5=1; ELSE VAR5=2;	IF V9=1 THEN VAR5=1; ELSE VAR5=2;
IF V4=2 THEN VAR6=2; ELSE VAR6=2;	IF V10=2 THEN VAR6=2; ELSE VAR6=2;
IF V8 IN (1,5) THEN VAR7=1; ELSE VAR7=2;	IF V6 IN (1,5) THEN VAR7=1; ELSE VAR7=2;
IF V9=1 THEN VAR8=1; ELSE VAR8=2;	IF V8=1 THEN VAR8=1; ELSE VAR8=2;
IF V1=2 THEN VAR9=2; ELSE VAR9=2;	IF V13 THEN VAR9=2; ELSE VAR9=2;
IF V2 IN (1,2,3) THEN VAR10=1; ELSE VAR10=2;	IF V11 IN (1,2,3) THEN VAR10=1; ELSE VAR10=2;

I have several sites that I need to recreate and would like to see if there is a simpler way to update the V#s instead of going through each code.

Thanks.

RVAS.

[Reeza]

Use arrays and then you can just change the list of variables in the array assuming the order is the same each time.

array _ques(*) v1 v2 v3 v4 v5 v6 v7 v8 v9 v10; *CHANGE ORDER OF VARIABLES HERE;
*array _ques(*) v12 v2 v6 v9 v5 v4 v8 v9 v1 v2;

IF _ques(1)=1 THEN VAR1=1; ELSE VAR1=2;
IF _ques(2)=2 THEN VAR2=2; ELSE VAR2=2;
IF _ques(3) IN (1,2,3) THEN VAR3=1; ELSE VAR3=2;
IF _ques(4) IN (1,5) THEN VAR4=1; ELSE VAR4=2;
IF _ques(5)=1 THEN VAR5=1; ELSE VAR5=2;
IF _ques(6)=2 THEN VAR6=2; ELSE VAR6=2;
IF _ques(7) IN (1,5) THEN VAR7=1; ELSE VAR7=2;
IF _ques(8)=1 THEN VAR8=1; ELSE VAR8=2;
IF _ques(9)=2 THEN VAR9=2; ELSE VAR9=2;
IF _ques(10) IN (1,2,3) THEN VAR10=1; ELSE VAR10=2;

[Reeza]

If all the data is one file, I would use a multidimensional array to do this all at once. You'll need to provide more information on how your data is structured( multiple files, single files) and how you will be doing this.

[rsva]

THought of array method, but I have around 100 variables to list in the array statement, so was not sure if its a good approach. I have multiple files, a separate file for each site. I also have a master file from which I get the mapping of the standard Q#s with site Q#s

[Reeza]

%macro recode_file(_indsn=, _outdsn=, question=);

*get variable order from first list;
proc sql noprint;
select &question into: quest_list separated by " "
from question_mapping
order by standard_question;
quit;

data &_outdsn.;
set &_indsn;

array _quest(*) &quest_list;

IF _ques(1)=1 THEN VAR1=1; ELSE VAR1=2;
IF _ques(2)=2 THEN VAR2=2; ELSE VAR2=2;
IF _ques(3) IN (1,2,3) THEN VAR3=1; ELSE VAR3=2;
IF _ques(4) IN (1,5) THEN VAR4=1; ELSE VAR4=2;
IF _ques(5)=1 THEN VAR5=1; ELSE VAR5=2;
IF _ques(6)=2 THEN VAR6=2; ELSE VAR6=2;
IF _ques(7) IN (1,5) THEN VAR7=1; ELSE VAR7=2;
IF _ques(8)=1 THEN VAR8=1; ELSE VAR8=2;
IF _ques(9)=2 THEN VAR9=2; ELSE VAR9=2;
IF _ques(10) IN (1,2,3) THEN VAR10=1; ELSE VAR10=2;

run;

%mend;

*example call;
%recode_file(_indsn=have, _outdsn=want, question=site_question1);

Then call the macro once for each file/dataset indicating the input data set, output data set and question/variable from the question mapping dataset. 

[ballardw]

Did you combine "sites" data from multiple sources? If so I would suggest a different approach to combining the data. RENAME the variables so they are the same and add variables to the data that indicate which source.

This sort makes me think much of this could be done when the data is read if you use a data step.