Learning SAS? Welcome to the exclusive online community for all SAS learners.

Rotating a dataset from long to wide using an array

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Rotating a dataset from long to wide using an array

Hi All,

 

I'm not new to SAS, but I don't use it very often so have found myself stuck getting an error message I can't think my way out of. I'm received a csv file dataset that is considered 'long' versus 'wide'. I want to rotate it from long to wide, so that the data entries of one person are entered all on one row, instead of multiple rows. 

 

My dataset lists a set of 9 different qualitative questions in one column (4th column in image below) with the associated answers in the next column (5th column in image below). I want to rotate the data set so the answers for one person are listed all in one row. I'll add back in the "questions" or variable names later once the dataset is rotated. 

 

In order to do this, I thought I should use an array in my data step. Unfortunately, I keep getting this error message: ERROR: Array subscript out of range at line 80 column 2.

 

Anyone have any clue what I'm doing wrong?? I've seached and searched online but nothing has worked. Thanks for any assistance or ideas someone can give me!!

 

Below is my code with line numbers and below that is a sample of the dataset:

 

data rotate;
60 length username $ 29 firstname $ 13 lastname $ 17 variable $ 50 answer $ 70 email $ 38;
61 infile '/home/emurray060/sasuser.v94/022416-heartland_user_info_data_profile_fields.csv' dsd missover;
62 input username $ firstname $ lastname $ variable $ answer $ email $;
63 run;
64
65 proc print data=rotate noobs;
66 run;
67
68 PROC SORT DATA=ROTATE;
69 BY username firstname lastname;
70 run;
71
72 data rotated;
73 array made[9] variable1-variable9 _character_;
74 retain variable1-variable9;
75 set rotate;
76 by username;
77 if first.username then do i = 1 to 9;
78 made[i] = .;
79 end;
80 made[answer] = variable;
81 if last.username then output;
82 keep username firstname lastname variable1-variable9 email;
83 run;
 
data rotate;
60 length username $ 29 firstname $ 13 lastname $ 17 variable $ 50 answer $ 70 email $ 38;
61 infile '/home/emurray060/sasuser.v94/022416-heartland_user_info_data_profile_fields.csv' dsd missover;
62 input username $ firstname $ lastname $ variable $ answer $ email $;
63 run;
64
65 proc print data=rotate noobs;
66 run;
67
68 PROC SORT DATA=ROTATE;
69 BY username firstname lastname;
70 run;
71
72 data rotated;
73 array made[9] variable1-variable9 _character_;
74 retain variable1-variable9;
75 set rotate;
76 by username;
77 if first.username then do i = 1 to 9;
78 made[i] = .;
79 end;
80 made[answer] = variable;
81 if last.username then output;
82 keep username firstname lastname variable1-variable9 email;
83 run;
sample file screen shot.jpg

Accepted Solutions
Solution
‎03-29-2016 10:26 AM
Trusted Advisor
Posts: 1,300

Re: Rotating a dataset from long to wide using an array

data rotate;
 length username $ 29 firstname $ 13 lastname $ 17 variable $ 50 answer $ 70 email $ 38;
 infile '/home/emurray060/sasuser.v94/022416-heartland_user_info_data_profile_fields.csv' dsd missover;
 input username $ firstname $ lastname $ variable $ answer $ email $;
run;

proc sort data=rotate;
by username variable;
run;

data rotated;
array m[9] $ 70 variable1-variable9;

call missing(of m[*]);

do _n_=1 by 1 to 9 until (last.username); *this assumes every record always has all 9 questions;
 set rotate;
 by username;
  
 m[_n_]=answer;
end;

drop variable answer;
run;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Rotating a dataset from long to wide using an array

Answer is a character variable so you can't use it to index your array. 

Are you sure you only have up to 9 entries?

 

If you post the error it will probably help. 

Respected Advisor
Posts: 3,777

Re: Rotating a dataset from long to wide using an array


Reeza wrote:

Answer is a character variable so you can't use it to index your array. 

 


You can use a character variable as an array index as log as the value is convert to a number in the index range.

 

40         data _null_;
41            answer=' 1 ';
42            array x[3];
43            x[answer]=9;
44            put _all_;
45            run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      43:6   
answer=1 x1=9 x2=. x3=. _ERROR_=0 _N_=1
Respected Advisor
Posts: 3,777

Re: Rotating a dataset from long to wide using an array

[ Edited ]

Look at the LOG you should see a value for ANSWER= when the step was stopped.  I'm pretty sure the value of ANSWER will be something other than 1-9.

 

Plus this

 

 array made[9] variable1-variable9 _character_;

is not doing what you might think.  _CHARACTER_ is a null list with the placement of the array statement.  It's good that is is because you have defined made as numeric.

Moderator
Posts: 238

Re: Rotating a dataset from long to wide using an array

At first glance, it looks like you are passing a character variable (answer) as an array subscript.

 

Can you post a log of the error, and possibly even the source CSV file for us to practice on?

Solution
‎03-29-2016 10:26 AM
Trusted Advisor
Posts: 1,300

Re: Rotating a dataset from long to wide using an array

data rotate;
 length username $ 29 firstname $ 13 lastname $ 17 variable $ 50 answer $ 70 email $ 38;
 infile '/home/emurray060/sasuser.v94/022416-heartland_user_info_data_profile_fields.csv' dsd missover;
 input username $ firstname $ lastname $ variable $ answer $ email $;
run;

proc sort data=rotate;
by username variable;
run;

data rotated;
array m[9] $ 70 variable1-variable9;

call missing(of m[*]);

do _n_=1 by 1 to 9 until (last.username); *this assumes every record always has all 9 questions;
 set rotate;
 by username;
  
 m[_n_]=answer;
end;

drop variable answer;
run;
New Contributor
Posts: 3

Re: Rotating a dataset from long to wide using an array

 

FriedEgg, this WORKED!!! Thank you!!

New Contributor
Posts: 3

Re: Rotating a dataset from long to wide using an array

[ Edited ]

Sure! Below is the log file with the error message of my original code and I'm attaching the data file. 

 

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         
 57         data rotated;
 58         array made[9] variable1-variable9  _character_;
 59         retain variable1-variable9;
 60         set rotate;
 61         by username;
 62         if first.username then do i = 1 to 9;
 63         made[i] = .;
 64         end;
 65         made[answer] = variable;
 66         if last.username then output;
 67         keep username firstname lastname variable1-variable9 email;
 68         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       65:2   65:7   
 NOTE: Invalid numeric data, answer='Yes' , at line 65 column 7.
 ERROR: Array subscript out of range at line 65 column 2.
 variable1=. variable2=. variable3=. variable4=. variable5=. variable6=. variable7=. variable8=. variable9=. username=12acm01
 firstname=Annie lastname=Murphey variable=Do you work in a Medically Underserved Area (MUA)? answer=Yes
 email=murpheya@william.jewell.edu FIRST.username=1 LAST.username=0 i=10 _ERROR_=1 _N_=1
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: There were 2 observations read from the data set WORK.ROTATE.
 WARNING: The data set WORK.ROTATED may be incomplete.  When this step was stopped there were 0 observations and 13 variables.
 WARNING: Data set WORK.ROTATED was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1730.87k
       OS Memory           29608.00k
       Timestamp           03/29/2016 02:17:25 PM
       Step Count                        15  Switch Count  84
       Page Faults                       0
       Page Reclaims                     467
       Page Swaps                        0
       Voluntary Context Switches        284
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8
       
 
 69         
 70         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 
Occasional Learner
Posts: 1

Re: Rotating a dataset from long to wide using an array

I believe your statement should be

made[variable] = answer;

not

made[answer] = variable;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 692 views
  • 1 like
  • 6 in conversation