05-15-2016 07:36 PM
I have a SAS Data Set containing a Character Variable: 'Question' with values as follows:
DQ01, DQ02, DQ03,...,DQ59.
I would like to recode my variable 'Question' so that the values become;
SQ01, SQ02, SQ03,..., SQ59.
How can I recode the above variable using one or more of the following:
b. PROC Format
c. Data Steps ( If A then B Statements )
d. Other Constructs such as CASE
Thanks for your help
05-16-2016 04:48 AM
05-15-2016 10:11 PM
Your particular case doesn't lend itself to any of the mentioned methods.
Since your replacing a D with an S, the translate() function is sufficient. Or Substr as indicated.
05-15-2016 11:26 PM
Quick note, there's no CASE within a data step, that's the SQL method for implementing if-then logic.
Again, your situation is a simple recode there's no need for anything fancy. Here's a demo of the SUBSTR, TRANSLATE, and a CASE statement, though that would get long fast so it's not really a recommended method.
If your just interested in all the ways to recode a variable I suggest you search on Lexjansen.com, there's many papers written on how to implement the various ways, as well as why one may be better than another.
data want; set have; question2=question; question1=question; question = translate(question, 'S', 'D'); substr(question1, 1, 1)='S'; select(question2); when ('DQ01') question2='SQ01'; when ('DQ02') question2='SQ02'; when ('DQ03') question2='SQ03'; otherwise question2='ERROR'; end; run;
05-16-2016 04:47 AM
05-16-2016 04:34 AM
This is something that comes up time and time again. Having many columns is not ideal for any process. It may be useful in an output report, however at all other times working with long data will make your life so much easier. Take your problem, if your data looked like this (long format know as normalised):
data have; question="DQ01"; answer="Y"; output; question="DQ02"; answer="N"; output; question="DQ03"; answer="Y"; output; run;
Your problem is so simple to achieve:
data want; set have; substr(question,1,1)="S"; run;
No need for arrays, couting variables, changing structures etc. a simple statement. This is why programming with Normalised data is far prefereable than working with transposed data - nothing stopping you from transposing the above when you need it for report as the data you program with does not need to look like the output data.
Really can't understand where this need to work with transposed data comes from? Most standards that I have seen (take CDISC for instance) all use normalised datasets as its just far easier to work with.
05-16-2016 04:46 AM
05-16-2016 04:52 AM
Perhaps post some of your data (test) in the form of a datastep (so we can read it in), and what the output should look like. Am not really seeing the picture from the two posts.
05-16-2016 05:29 AM
05-16-2016 06:02 AM
We all work under NDA's, I am asking for some test data - which mirrors your structure, and what the output should look like. Something simple such as (which cover each occurence):
data have1; var1="abc"; var2=23; output; var1=...; run;
For each of the datasets involved. The reason is I can't see the picture of what is to happen and from what. You say you have a variable called Question, then the values go down the dataset yes? So none of your original questions match. You later mention that you need to merge datasets - there is not indication of what each of the datasets look like, so can't tell you how to do it. If they all have QUESTION and RESULT only, then you might not need to change the variable at all:
proc sql; create table WANT as select COALESCE(A.QUESTION,B.QUESTION...) as QUESTION, A.RESULT as RESULT_A, B.RESULT as RESULT_B, ... from HAVE A full join HAVE B on substr(A.QUESTION,2)=substr(B.QUESTION,2) ... quit;
05-16-2016 08:21 AM
05-16-2016 08:29 AM
Sorry, no attachment. I wouldn't be able to download certain files due to security risk anyways, and the other files I wouldn't type in. Please see this post where Reeza provides information on how to create a datastep for test data.
05-16-2016 03:47 PM
Need further help from the community? Please ask a new question.