BookmarkSubscribeRSS Feed
er_awasthi
Calcite | Level 5
Input

Obs Name
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
1 13
2 3
2 4
2 5
2 6
2 7
2 7
2 8

Requirment - For every similar value of Obs - I want one single row such that it concatenates any 10

Names seperated by comma.



Output should come like this :-
obs Name
1 2,3,4,5,6,7,8,9,10,11
2 3,4,5,6,7,7,8
3 REPLIES 3
er_awasthi
Calcite | Level 5
I wrote this code. set 1 has Obs and Name as variables


DATA SET2;
SET SET1;
BY OBS;
RETAIN STR1;
LENGTH STR1 $ 100.;
LENGTH STR2 $ 100.;
IF (FIRST.OBS AND LAST.OBS) THEN
DO;
STR1 = NAME;
END;
ELSE
DO;
IF FIRST.OBS THEN
DO;
STR1 = NAME;
END;
ELSE
DO;
STR2 = STR1;
STR1 = ' ';
STR1 = TRIM(STR2)||','||TRIM(NAME);
END;
END;
IF LAST.OBS THEN
DO;
OUTPUT;
STR1 = ' ';
END;
DROP NAME STR2;
PROC PRINT DATA = SET2 NOOBS;

But there is one bug in this code.

I'm getting output as
Obs Name
1 2,3,4,5,6,7,8,9,10,11,12,13
2 3,4,5,6,7,7,8

But i want any 10 names in output for similar values of obs

The output should be

Output should come like this :-
obs Name
1 2,3,4,5,6,7,8,9,10,11
2 3,4,5,6,7,7,8


Can anybody help me out to limit the output to any 10 names for similar values of obs
ieva
Pyrite | Level 9
Hi,

I think adding these couple of lines works as you wished:

DATA SET2 (drop=num);
SET SET1;
BY OBS;
RETAIN STR1;

LENGTH STR1 $ 100.;
LENGTH STR2 $ 100.;

if first.obs then num=0;
num+1;


IF (FIRST.OBS AND LAST.OBS) THEN
DO;
STR1 = NAME;
END;
ELSE
DO;
IF FIRST.OBS THEN
DO;
STR1 = NAME;
END;

ELSE IF num le 10 then DO;

STR2 = STR1;
STR1 = ' ';
STR1 = TRIM(STR2)||','||TRIM(NAME);
END;
END;
IF LAST.OBS THEN
DO;
OUTPUT;
STR1 = ' ';
END;
DROP NAME STR2;
run;
Patrick
Opal | Level 21
this should work as well:

data have;
Input Obs Name $;
datalines;
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
1 13
2 3
2 4
2 5
2 6
2 7
2 7
2 8
;

data want (keep=obs string);
set have;
by obs;
length string $ 1000;
retain string;
counter+1;
if first.obs then string=strip(Name);
else do;
if counter LE 10 then string=cats(string,',',Name);
end;
if last.obs then do;
output;
counter=0;
end;
run;

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!

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.

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
  • 3 replies
  • 682 views
  • 0 likes
  • 3 in conversation