topic Re: Need help how i can resolve this kind of scenario in SAS Programming

Need help how i can resolve this kind of scenario

ganeshmule — Thu, 13 Jun 2024 12:01:49 GMT

data set values are below:?
data have ;

input ID VALUE$;

datalines;

101 V1
101 V2
101 V3
102 V4
102 V5

;

run;

I want output in new data set like below:

ID VALUE
101 V1,V2,V3
102 V4,V5

please help thanks in advance

Poor Subject Line; Better Subject Line Needed

PaigeMiller — Thu, 13 Jun 2024 12:00:50 GMT

Please go back to your post and change the subject line to provide a meaningful subject line that briefly describes the problem you are trying to solve. Saying you need help or how to fix/resolve an issue isn't a good subject line. Thanks!

Re: Poor Subject Line; Better Subject Line Needed

ganeshmule — Thu, 13 Jun 2024 12:02:50 GMT

Thanks corrected

Re: Poor Subject Line; Better Subject Line Needed

PaigeMiller — Thu, 13 Jun 2024 12:16:26 GMT

We're going to try to help you, but you have to help us too. You didn't briefly describe the problem you are trying to solve. Saying "need help" or "you can't resolve a problem" is not a good subject line. This forum would be useless if all post subject lines were: "Help" "I need help" "Can't get this to work" "Not Working" "Help".

Your previous posts did briefly describe the problem you are trying to solve, so I know you can do it.

Re: Need help how i can resolve this kind of scenario

maguiremq — Thu, 13 Jun 2024 12:26:11 GMT

You could call this combining values by ID. I'm sure there's a better name.

data have ;
input  ID VALUE$;
datalines;
101 V1
101 V2
101 V3
102 V4
102 V5
;
run;

data want (rename = (values = value) drop = value);
	do until (last.id);
		set have;
		by id;
		put value = ;
		put id = ;
		if first.id then values = value;
			else if not first.id then do;
				values = catx(" ", values, value);
				put values = ;
				put id = ;
			end;
	end;
run;

ID	value
101	V1 V2 V3
102	V4 V5

Note that your data set will need to be sorted by ID.

I used the PUT statements so that you can look at the log to see how it is working internally.

 69         data want (rename = (values = value) drop = value);
 70         do until (last.id);
 71         set have;
 72         by id;
 73         put value = ;
 74         put id = ;
 75         if first.id then values = value;
 76         else if not first.id then do;
 77         values = catx(" ", values, value);
 78         put values = ;
 79         put id = ;
 80         end;
 81         end;
 82         run;
 
 VALUE=V1
 ID=101
 VALUE=V2
 ID=101
 values=V1 V2
 ID=101
 VALUE=V3
 ID=101
 values=V1 V2 V3
 ID=101
 VALUE=V4
 ID=102
 VALUE=V5
 ID=102
 values=V4 V5
 ID=102

Re: Need help how i can resolve this kind of scenario

andreas_lds — Thu, 13 Jun 2024 12:48:33 GMT

I am 100% sure that problem very similar to yours have been solved here at least a dozen times ...

proc transpose data=have out=transposed(drop=_name_);
    by id;
    var value;
run;

data want;
    set transposed;
    length value $ 100;

    value = catx(",", of col:);

    drop col:;
run;