SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mariko5797
Pyrite | Level 9

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

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;

View solution in original post

3 REPLIES 3
ErikLund_Jensen
Rhodochrosite | Level 12

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
Obsidian | Level 7

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
Calcite | Level 5

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 860 views
  • 0 likes
  • 4 in conversation