topic Re: transpose data by re-arranging data in columns (?) in SAS Procedures

transpose data by re-arranging data in columns (?)

erickbernard — Thu, 30 Oct 2014 01:56:09 GMT

Hi there,

Here I am again with my neverending transposition questions. Though, I'm not sure if this is necesarily a transposition problem.

Here's what I have;

item1	item2
CompanyABC	organisation
domestic	location
cash	type
123	assets
456	liabilities

and this is what I want;

organisation	item_name	value
CompanyABC	domestic \|\| cash \|\| assets	123
CompanyABC	domestic \|\| cash \|\| liabilities	456

and here's a dummy code;

data have;

infile cards dsd;

input (item1-item2) (:$16.);

cards;

CompanyABC,organisation

domestic,location

cash,type

123,assets

456,liabilities

;

run;

If you excuse me, I'm taking a break from this for the next 30 minutes and will have a cup of coffee.

Re: transpose data by re-arranging data in columns (?)

PGStats — Thu, 30 Oct 2014 02:41:25 GMT

This should do:

data want;

set have;

select (item2);

when ("location") location = item1;

when ("type") type = item1;

when ("organisation") do;

organisation = item1;

call missing(location, type);

end;

otherwise do;

item_name = catx(" || ", location, type, item2);

value = item1;

output;

end;

retain organisation location type;

keep organisation item_name value;

run;

Re: transpose data by re-arranging data in columns (?)

data_null__ — Thu, 30 Oct 2014 03:48:36 GMT

"It's déjà vu all over again."

data have;
   input (item1 item2)(:$16.); 
cards; 
CompanyABC organisation
domestic location
cash type
123 assets
456 liabilities
;;;;
   run; 
proc transpose out=have2;
   var item1;
   id item2;
   run; 
proc print; 
   run; 
proc transpose out=have3 name=item prefix=value;
   by organisation location type;
   var assets liabilities;
   run; 
proc print; 
   run;

Re: transpose data by re-arranging data in columns (?)

RaviKommuri — Thu, 30 Oct 2014 07:42:58 GMT

Another way......(may not be optimized code)

DATA WANT(KEEP=ORG ITEM_NAME VALUE);

SET HAVE;

LENGTH ORG $16. ITEM_NAME $40. VALUE 3.;

IF UPCASE(ITEM2) = 'ORGANISATION' THEN ORG = ITEM1;

IF UPCASE(ITEM2) = 'LOCATION' THEN LOCATION = ITEM1;

IF UPCASE(ITEM2) = 'TYPE' THEN TYPE = ITEM1;

IF UPCASE(ITEM2) = 'ASSETS' OR UPCASE(ITEM2) = 'LIABILITIES' THEN MOD = ITEM2;

IF UPCASE(ITEM2) = 'ASSETS' OR UPCASE(ITEM2) = 'LIABILITIES' THEN VALUE = ITEM1;

RETAIN ORG LOCATION TYPE;

IF VALUE = . THEN DELETE;

ITEM_NAME = LOCATION||TYPE||MOD;

RUN;

Re: transpose data by re-arranging data in columns (?)

erickbernard — Fri, 31 Oct 2014 00:00:34 GMT

thank you data_null; that's exactly what I needed. One of these days, I need to do a cheat sheet for transposing data because I'm having a lot of difficulty visualiising it in my mind and as a result coming empty handed. Thank you again.