topic Re: need to change data in SAS Programming

need to change data

helpmedoubts — Sat, 01 Aug 2020 12:58:51 GMT

data have;
input fruit$ fru$ fco;
datalines;
banan f 10
broc v 14
apple f 15
;
run;
proc sort data = have; by fruit ;
proc transpose data = have out = fruit1;
by fruit ;
var fco ;
id fru ;
run;

now i need to transpose it back to get the original data as it was.

Re: need to change data

hhinohar — Sat, 01 Aug 2020 16:03:27 GMT

Here is a quick sample.

data want(keep=fruit fru fco);
	set fruit1;
/* 	put sas variables into array */
	array tmp[2] _numeric_;
/* 	loop through array and put everything back into place */
	do i=1 to dim(tmp);
		fru=vlabel(tmp[i]);
		fco=tmp[i];
		if tmp[i] ne . then	output;
	end;
run;

Re: need to change data

helpmedoubts — Sat, 01 Aug 2020 16:09:26 GMT

Thank you. But i need the output using proc transpose only

Re: need to change data

Patrick — Sun, 02 Aug 2020 00:20:52 GMT

@helpmedoubts wrote:

Thank you. But i need the output using proc transpose only

Sounds like an assignment problem. What have you tried already and where do you get stuck?

Re: need to change data

hhinohar — Sun, 02 Aug 2020 02:56:37 GMT

Sorry for misreading the intention.

I tried a few option and I guess below is what is possible with proc transpose.
First I tried proc transpose with var and by option but that generated n*n obs with fruit and fco group
which led to outputting unnecessary missing observations.

So I added where= data set option that omits missing when outputting result data set and that generated the desired output.

I hope this helps!

data have;
input fruit$ fru$ fco;
datalines;
banan f 10
broc v 14
apple f 15
;
run;
proc sort data = have; by fruit fru;
proc transpose data = have out = fruit1;
	by fruit ;
	var fco ;
	id fru ;
run;

/* omit missing with where= dataset option  */
/* as transpose will output missing obs with n*n merge (fruit fco)*/
proc transpose data = fruit1 out = want(rename=(_name_=fru) where=(fco ne .));
	var f v;
	by fruit;
run;

Re: need to change data

helpmedoubts — Sun, 02 Aug 2020 03:33:11 GMT

Just trying to understand the concept in all ways. When I tried I was getting duplicates as in instead of 3 obs as output i was getting 6obs

Re: need to change data

helpmedoubts — Sun, 02 Aug 2020 03:36:11 GMT

Thank you. Both the programs worked. But i was looking for the second program(transpose). Sorry for the confusion. was getting duplicates in output, didn't think of using where option. Thanks again.

Re: need to change data

hhinohar — Wed, 05 Aug 2020 02:22:15 GMT

my pleasure.