BookmarkSubscribeRSS Feed
slivingston1
Calcite | Level 5

I am working with an interesting data set, however I am having difficulty cleaning it up due to certain imported formatting issues I am encountering.

The data set is expanded with multiple columns indicating response to surveys. I am trying to condense the data set columns in SAS to have responses to each question unique to each column.I am a little confused on where to start for the coding. For instance:

Data set now:


Respondent ID    K   1   2  3   4 

1                      1  

2                               3

3                           2

4                       1

5                                       5

____________________

I would like to have

Respondent ID  Grade

1                      1

2                      3

3                      2

4                      1

5                      5

Any ideas?


I have attached the sample Excel document with the dataset for further details.


Thanks in advance!

4 REPLIES 4
art297
Opal | Level 21

You could just use the coalesce function.  E.g.:

data have;

  input id k _1 _2 _3;

  cards;

1 . . 3 .

2 1 . . .

3 . . . 4

;

data want (keep=id grade);

  set have;

  grade=coalesce(of k--_3);

run;

Astounding
PROC Star

Another possibility:  read in the data yourself, in a DATA step, rather than importing it from Excel.  When you do that, omit the DSD option from the INFILE statement, so that multiple consecutive delimiters get treated as a single delimiter.  In that case, you could simply use:

input respondent_ID $ grade;

Good luck.

Ksharp
Super User

I like Art's code. Why do you have to use proc transpose ?

data have;
  input id k _1 _2 _3;
  cards;
1 . . 3 .
2 1 . . .
3 . . . 4
;
run;
proc transpose data=have out=want(drop=_name_ where=(col1 is not missing)) ;
by id;
var k _1 _2 _3;
run;

Ksharp

ballardw
Super User

I'd say go back to SurveyMonkey (or other survey program, example sure looks like SurveyMonkey to me) and look at your data export options. There is one that provides one response per question instead of this format.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 871 views
  • 1 like
  • 5 in conversation