BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

 

Hello everyone,

 

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

 

data.png................................

 

i have different data set too.

 

data2.png

 

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;

 

data3.png

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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
PROC Star

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
Opal | Level 21

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;
PG
Ksharp
Super User
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
Lapis Lazuli | Level 10

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1136 views
  • 0 likes
  • 6 in conversation