topic Help Splitting a String and reshaping a data set in New SAS User

Help Splitting a String and reshaping a data set

kirkends — Fri, 04 Jun 2021 16:08:59 GMT

I am new to SAS and need some help reshaping a data set and am not sure where to start. My data set is an excel. I think I need to use an Array but am not sure how to logic it.

HAVE

Unique ID	IN	OUT	N/A	N/O
1	1, 2, 3, 4, 5, 6, 7, 10, 11, 13, 15, 16, 17, 21, 22, 24, 28	30	14, 18, 19, 20, 23, 25, 26, 27, 29	8, 9, 12
2	30
3	1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 13, 15, 16, 17, 21, 22, 28		14, 18, 19, 20, 24, 25, 26, 27, 29	8, 12, 23
4	1, 2, 3, 4, 5, 10, 11, 13, 14, 15, 16, 17, 22, 28		18, 19, 20, 21, 25, 26, 27, 29	6, 7, 8, 9, 12, 23, 24
5	1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 13, 15, 16, 17, 20, 22, 23, 28	10	14, 18, 19, 24, 25, 26, 27, 29	12, 21

Want

uniqueID

V10

V11

V12

V13

V14

V15

V16

V17

V18

V19

V20

V21

V22

V23

V24

V25

V26

V27

V28

V29.....

V30

N/O

N/A

N/O

N/A

N/O

N/A

N/O

N/A

N/O

N/A

OUT

N/O

N/A

N/O

N/A

Re: Help Splitting a String and reshaping a data set

ballardw — Fri, 04 Jun 2021 16:29:43 GMT

Before going about reshaping the data you might describe just how you intend to use the resulting data set.

There may be better ways to reshape it based on intended use.

Have you created a SAS dataset?

Are the variable names as you show the column headings? I ask because by default SAS will not like space or / characters in variable names.

Something like this should work but again, how your actually intend to use the data this may not be the best case.

data want;
   set have;
   array v(30) $ 5;
   do i=1 to countw(in);
      pos=input(scan(in,i),2.);
      v[pos]='IN';
   end;
   do i=1 to countw(out);
      pos=input(scan(out,i),2.);
      v[pos]='OUT';
   end;
   do i=1 to countw(n_a); /*what ever the actual name of the N/A becomes*/
      pos=input(scan(n_a,i),2.);
      v[pos]='N/A';
   end;
   do i=1 to countw(n_o); /* same note about variable name*/
      pos=input(scan(n_o,i),2.);
      v[pos]='N/O';
   end;
   keep uniqueid v: ;
run;

Re: Help Splitting a String and reshaping a data set

kirkends — Fri, 04 Jun 2021 17:09:54 GMT

@ballardw I have about 7 data sets all answering the same questions, but all entered in unique and creative formats. This is the most common entry layout but it is not the same across each dataset. I am hoping to reshape the dataset into the requested final format, merge all 7 sets (for sharing) and eventually provide frequency counts for IN OUT N/A N/O per V#.