topic Re: wide to long with first set of columns stay the same in SAS Programming

wide to long with first set of columns stay the same

geneshackman — Mon, 26 Jun 2023 20:03:16 GMT

I'm sure this should be simple, but it's taking me a while to find it. I want to change a wide data set to a long data set. However, I want the first sets of columns to stay the same. I'm attaching two made up, fictional data sets. Testwide.xlsx is the input data set, that has a wide format, and I want to change it to testlong.xlsx, which would be the output data set, with a long format.

I can find lots of websites on changing wide to long, but only when you pivot or transpose starting at the first variable, and all the other ones transpose. I want the first set of columns to stay the same for each row.

Thanks!

Re: wide to long with first set of columns stay the same

Reeza — Mon, 26 Jun 2023 20:00:19 GMT

You can set those as multiple by variables.

Re: wide to long with first set of columns stay the same

geneshackman — Mon, 26 Jun 2023 20:08:25 GMT

Hi Reeza. Thanks for responding. What would I do, in SAS, to change the wide data set into the long data set. You mean something like this?

proc transpose data=testwide out=testlong;

by county group indicator;

run;

thanks

Re: wide to long with first set of columns stay the same

Reeza — Mon, 26 Jun 2023 20:16:34 GMT

I can't download excel at the moment, so no idea what your samples look like. It's much easier if you include the sample data in your posts.

Re: wide to long with first set of columns stay the same

geneshackman — Mon, 26 Jun 2023 20:19:38 GMT

I usually don't include in my posts because they often don't show up the way I have them, but here goes. This is the wide version

County	group	indicator	city	burbs	rural	Total
Albany	capital	wijits	87	45	23	40
Da Bronx	NYC	wijits	23	54	32	47
Albany	Capital	thingies	12	18	13	15
Da Bronx	NYC	thingies	15	10	15	12

I want to change it to this long version

County	group	indicator	location	value
Albany	capital	wijits	city	87
Albany	capital	wijits	burbs	45
Albany	capital	wijits	rural	23
Albany	capital	wijits	total	40
Da Bronx	NYC	wijits	city	23
Da Bronx	NYC	wijits	burbs	54
Da Bronx	NYC	wijits	rural	32
Da Bronx	NYC	wijits	total	47
Albany	Capital	thingies	city	12
Albany	Capital	thingies	burbs	18
Albany	Capital	thingies	rural	13
Albany	Capital	thingies	total	15
Da Bronx	NYC	thingies	city	15
Da Bronx	NYC	thingies	burbs	10
Da Bronx	NYC	thingies	rural	15
Da Bronx	NYC	thingies	total	12

Re: wide to long with first set of columns stay the same

Reeza — Mon, 26 Jun 2023 20:23:39 GMT

Your sample code looks correct, I'd explicitly add the variables to transpose via a VAR statement but I think it will default to everything else.

Re: wide to long with first set of columns stay the same

geneshackman — Mon, 26 Jun 2023 20:28:44 GMT

I was getting 0 rows of output until i put in the var line, so that looks right. Thanks