How to concatenate different rows?

[mariko5797]

I want to make a variable (WANT.VISITS) that lists all the visits (HAVE.VISNO) present in one dataset (HAVE), so that the final dataset (WANT) only has one row per subject.

Essentially I am trying to make an analysis populations dataset (POP) and want to be able to identify visit-specific exclusions for each subject, e.g. out-of-window visits. 

/*Current Dataset Format*/
data have;
input ID $ VISNO $ RESULT @@;
cards;
A 01 .
A 02 7
A 03 9
A 04 12
A 05 10
B 01 3
B 02 7
B 04 9
B 05 11
C 01 8
C 02 9
C 03 12
;
run;

/*Desired Dataset*/
data want;
input ID $ VISITS @@;
cards;
A 01,02,03,04,05
B 01,02,04,05
C 01,02,03
;
run;

[ErikLund_Jensen · Accepted solution]

Hi @mariko5797 

This vill do it, given the input must be sorted in ID and VISNO:

data want;
  set have (drop=RESULT);
  by ID;
  length VISITS $200;
  retain VISITS;
  VISITS = catx(',',VISITS,VISNO);
  if last.ID then do;
    output;
    VISITS = '';
  end;
run;

[vijaypratap0195]

I believe you can do this by using proc transpose procedure.

1. transpose the dataset by=id and id=visno

2. then concate all visno01-visno0n using catx() function.

Hope this code will help you getting the desired result.

proc transpose data=have prefix=visits out=tmp;
by id;
id visno;
var visno;
run;

data want;
set tmp;
visits = catx(',', of visits0:);
keep id visits;
run;

[chaeunwoo]

I currently have a sas data set where each observation has 3 attributes. However, the text input has 5 per row with multiple rows. How do I tell sas to read the first 3 values in the row as an entry, then the next three, etc before moving to the next line? Basically, if one input row looks like this:

4 4 10 2 2 10 5 5 10 6 6 10 9 9 10

I want it to end up like this in the sas dataset:

4 4 10

2 2 10

5 5 10

6 6 10

9 9 10.