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!
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
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 |
Hi
I was getting 0 rows of output until i put in the var line, so that looks right. Thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.