Update colums from one data set to another data set

[turcay]

Hello everyone,

I have forty "Question" columns in my data set. Sample of data set is below.

................................

i have different data set too.

My purpose is update the Current column values same as the Intended column values. After i wrote the code below. I could made it. But just for one column(Question1) How about other columns. Is it possible to change them all ?

proc sql;
update work.ChangeValue set Question1=(select Intended from work.work.ChangeValue2 
where Variable="Question1")
where Question1=(select Current from work.work.ChangeValue2 
where Variable="Question1");
quit;

Thank you.

[Tom]

Can you attempt to explain what you want in another way?  Preferable with actual before and after data.

It looks like you just want to force the values of the Q variables to be the same for all observations.

Also your first dataset does not appear to have any id variable to uniquely identify the rows. What is the meaning of the VALUE variable?  Is it important?

[Astounding]

It sounds like you want the equivalent of this:

if question1=2 then question1=1;

if question3=4 then question3=3;

if question5=1 then question5=2;

But you want the actual set of changes to be based on your second data set.

Does that sound about right?

Are you locked in to using SQL UPDATE, or would any working approach be OK?

[PGStats]

Generate the SQL update statements dynamically (lazy typist version):

data test;
input v q1 q2 q3;
datalines;
10 1 2 3
20 2 3 4 
30 3 4 1
40 2 3 4
;

data change;
input v $ i c;
datalines;
q1 4 2
q3 2 1
;

proc print data=test; run;

data _null_;
set change;
if _n_ = 1 then call execute('Proc sql;');
call execute(catx(" ", 'update test set', v, '=', i, 'where', v, '=', c, ';'));
run;

proc print data=test; run;

[Ksharp · Accepted solution]

Under Astounding Help. Maybe I understand what you mean.You'd better post some SAS data code , NOT photo, No one will type these data for you . Try this one : (using PG's data)

data test;
input value q1 q2 q3;
datalines;
10 1 2 3
20 2 3 4 
30 3 4 1
40 2 3 4
;
run;
data change;
input variable $ i c;
datalines;
q1 4 2
q3 2 1
;
run;
data want;
 if _n_ eq 1 then do;
  if 0 then set change;
  declare hash ha(dataset:'change');
  ha.definekey('variable','c');
  ha.definedata('i');
  ha.definedone();
 end;
set test;
array x{*} q1-q3;
do k=1 to dim(x);
 variable=vname(x{k});
 c=x{k};
 if ha.find()=0 then x{k}=i;
end;
drop k variable i c;
run;

[turcay]

Thanks everyone. I posted photos because i think it is the best way to tell what i try to do and i also post my SAS code to describe you. Everyone helps me within this this period i imporove my skills. I try to find best way to use memory more efficiency. I guess less data steps or proc sql steps more efficiency. Thanks again.

[ballardw]

I am making a big guess here but this looks like it may be a recoding where the data values represent question answer codes and the codes used to represent the same value (Yes/No or Always/sometimes/never or such) changed between data collection periods.

IF that is the case then I would suggest using custom informats for each question response group.

For example if I had a variable that in a previous collection cycle was coded as:

1 = Often

2 = Sometimes

3 = Seldom

and we changed the coding to reflect something like:

1 = Always

2 = Usually

3 = Sometimes

4 = Not often

5 = Never

I might want to match the previous response of 1 (often) to the current coding of 2 (usually). I'm not saying this is a good idea, just to set the stage.

So to re-code the old data to the new standard:

proc format;
invalue recode
1 = 2
3 = 4
other= _same_; /* assumes some values map to the same numeric*/
run;

/*Use as :*/
Data want;
   set have ;
   Question1 = input(question1,recode.);
run;

This approach works very well if you have multiple questions / data values with the same scale.

Note, this will generate a message about numeric to character conversions.