topic Update colums from one data set to another data set in SAS Programming

Update colums from one data set to another data set

turcay — Thu, 17 Sep 2015 22:27:09 GMT

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.

Re: Update colums from one data set to another data set

Tom — Fri, 18 Sep 2015 00:44:21 GMT

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?

Re: Update colums from one data set to another data set

Astounding — Fri, 18 Sep 2015 01:30:24 GMT

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?

Re: Update colums from one data set to another data set

PGStats — Fri, 18 Sep 2015 02:28:35 GMT

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;

Re: Update colums from one data set to another data set

Ksharp — Fri, 18 Sep 2015 05:34:39 GMT

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;

Re: Update colums from one data set to another data set

ballardw — Fri, 18 Sep 2015 15:53:28 GMT

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.

Re: Update colums from one data set to another data set

turcay — Fri, 18 Sep 2015 19:58:59 GMT

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.