erickbernard Tracker

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.

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: Transposing Data (a bit more complicated this time around)

erickbernard — Tue, 28 Oct 2014 01:55:05 GMT

in retrospect, this is the way to go i think. there are more changes in the data i received. so hopefully i can modify this to work with that.

Transposing Data (a bit more complicated this time around)

erickbernard — Mon, 27 Oct 2014 10:38:49 GMT

Hi everyone,

I have a transposition problem which I can't figure out how to solve. So SAS forum, you are my only hope at this point.

Here we go. Suppose I have the following data;

id	Item_name	Sequence	Type_name
01	ABC	1	company
01	domestic	1	direct
01	cash	1	type
01	1000	1	classA
01	500	1	classB
01		1	indirect
01		1	type
01		1	classA
01		1	classB
01	ABC	2	company
01	international	2	direct
01	bond	2	type
01	2000	2	classA
01	1500	2	classB
01		2	indirect
01		2	type
01		2	classA
01		2	classB
01	ABC	3	company
01	domestic	3	direct
01	property	3	type
01	5000	3	classA
01	3500	3	classB
01		3	indirect
01		3	type
01		3	classA
01		3	classB
02	DEF	1	company
02		1	direct
02		1	type
02		1	classA
02		1	classB
02	domestic	1	indirect
02	cash	1	type
02	4000	1	classA
02	3000	1	classB
02	DEF	2	company
02		2	direct
02		2	type
02		2	classA
02		2	classB
02	international	2	indirect
02	bond	2	type
02	7000	2	classA
02	6500	2	classB
02	DEF	3	company
02		3	direct
02		3	type
02		3	classA
02		3	classB
02	domestic	3	indirect
02	property	3	type
02	9000	3	classA
02	8500	3	classB

and trying to transform it into the following;

id	company	type	value
01	ABC	direct=domestic, type=cash, classA>0	1000
01	ABC	direct=domestic, type=cash, classB>0	500
01	ABC	direct=international, type=bond, classA>0	2000
01	ABC	direct=international, type=bond, classB>0	1500
01	ABC	direct=domestic, type=property, classA>0	5000
01	ABC	direct=domestic, type=property, classB>0	3500
02	DEF	indirect=domestic, type=cash, classA>0	4000
02	DEF	indirect=domestic, type=cash, classB>0	3000
02	DEF	indirect=international, type=bond, classA>0	7000
02	DEF	indirect=international, type=bond, classB>0	6500
02	DEF	indirect=domestic, type=property, classA>0	9000
02	DEF	indirect=domestic, type=property, classB>0	8500

Notice, there is a structure in the data. If for example, type is direct, it can't be indirect in that sequence. What I'm struggling with is the "logic" I want to implement,

if (type_name=direct and item_name=domestic) and type_name=classA then value=1,000.

I'll post the sample code for the dummy data above as well if it helps;

data have;

infile cards dsd;

input id:$2. item_name :$13. sequence :$2. type_name :$8.;

cards;

01,ABC,1,company

01,domestic,1,direct

01,cash,1,type

01,1000,1,classA

01,500,1,classB

01,,1,indirect

01,,1,type

01,,1,classA

01,,1,classB

01,ABC,2,company

01,international,2,direct

01,bond,2,type

01,2000,2,classA

01,1500,2,classB

01,,2,indirect

01,,2,type

01,,2,classA

01,,2,classB

01,ABC,3,company

01,domestic,3,direct

01,property,3,type

01,5000,3,classA

01,3500,3,classB

01,,3,indirect

01,,3,type

01,,3,classA

01,,3,classB

02,DEF,1,company

02,,1,direct

02,,1,type

02,,1,classA

02,,1,classB

02,domestic,1,indirect

02,cash,1,type

02,4000,1,classA

02,3000,1,classB

02,DEF,2,company

02,,2,direct

02,,2,type

02,,2,classA

02,,2,classB

02,international,2,indirect

02,bond,2,type

02,7000,2,classA

02,6500,2,classB

02,DEF,3,company

02,,3,direct

02,,3,type

02,,3,classA

02,,3,classB

02,domestic,3,indirect

02,property,3,type

02,9000,3,classA

02,8500,3,classB

;

run;

Hopefully someone will be able to crack this.

Re: Frequency of Characters in a string

erickbernard — Mon, 27 Oct 2014 00:55:16 GMT

well hopefully I'm not cluttering this thread by another post. Anyway, I had a similar problem some time ago, and my solution was to summarize data first, then remove all the legal characters. here's my sample code (using the testdata above, unfortunately I can't paste from SAS window, and hence the ugly formatting, sorry about that);

/*first summarize data*/

proc sort data=test by testvar; run;

proc summary data=test; by testvar; output out=test2(drop=_type_);run;;

/*then remove "legal" characters*/

data test3; set test2;

original=testvar;

testvar=upcase(testvar);

testvar=compress(testvar,"ABCDEFGHIJKLMNOPQRSTUVWXYZ");

run;

Re: Transposing Data-

erickbernard — Fri, 24 Oct 2014 12:06:51 GMT

i think this is it. I never really thought I could do this in two steps (to me it seems ingenious now ). really appreciate the reply.

Transposing Data-

erickbernard — Fri, 24 Oct 2014 10:56:42 GMT

Hi everyone,

I'm trying to transpose the following table into a certain format described below. I haven't been able to figure it out, so decided to seek some advice. So here's what data looks like;

field1	field2	date
CompanyABC	company	Mar-14
432	asset	Mar-14
54534	liabilities	Mar-14
4324	income	Mar-14
2324	expenses	Mar-14
CompanyABC	company	Jun-14
5435	asset	Jun-14
3435	liabilities	Jun-14
987	income	Jun-14
345	expenses	Jun-14
CompanyDEF	company	Mar-14
533	asset	Mar-14
6534	liabilities	Mar-14
5478	income	Mar-14
3456	expenses	Mar-14
CompanyDEF	company	Jun-14
754	asset	Jun-14
435	liabilities	Jun-14
343	income	Jun-14
453	expenses	Jun-14

and this is what I'm after;

	company	Mar-14	Jun-14
asset	CompanyABC	432	5435
liabilities	CompanyABC	54534	3435
income	CompanyABC	4324	987
expenses	CompanyABC	2324	345
asset	CompanyDEF	533	754
liabilities	CompanyDEF	6534	435
income	CompanyDEF	5478	343
expenses	CompanyDEF	3456	453

Can anyone help?

Re: Combining multiple Excel files while maintaining their identities

erickbernard — Tue, 02 Sep 2014 05:52:37 GMT

Hi Scott,

Thank you for the detailed reply and appreciate the sample code as well.

Yes, I was referring to the input files. I'm planning to keep them unedited/untouched and carry on with data preparation/analysis on a single platform. If this point wasn't clear, I apologize.

Introducing a step utilizing VBA, Powershell etc, adds an extra step to this process (from my point of view at least) that I'd rather avoid. On top of that, I've never used those systems before.

Finally, since I was able to solve my problem in R and given that I can complete my analysis in R, I've accomplished what I was after, that is, completing this process in a single platform.

Re: Combining multiple Excel files while maintaining their identities

erickbernard — Mon, 01 Sep 2014 04:26:11 GMT

I've managed to write the code in R, and I guess wil stick with that. I'm not sure about the etiquette of posting code for other packages, so I'll wait for a while to see if anyone objects me posting R code here.

Re: Combining multiple Excel files while maintaining their identities

erickbernard — Mon, 01 Sep 2014 01:24:54 GMT

Hi Scott,

Thank you for your reply. The data comes in CSV format and all the processing/analysis will be done in SAS. I have a strong preference to keep all processing/analysis in a single platform to maintain version control and easy reproducibility. So I'm planning to keep the CSV files as is.

Combining multiple Excel files while maintaining their identities

erickbernard — Mon, 01 Sep 2014 00:37:22 GMT

Hi everyone,

I want to ask something that I've been struggling with it for the last day or so. I've seen other posts similar to mine on this forum but for some reason I haven't been able to utilize them.

Alright, so I have multiple CSV files (with identical variables) that I want to merge while maintaining their identity. I should add, I only want to merge some of the files.

An example will make it clear. Suppose I have the following files;

2014_1.csv

2014_2.csv

2014_3.csv

aa_2014_1.csv

aa_2014_2.csv

aa_2014_3.csv

etc.

When I merge (say the top 3 files), I want to have another variable with the original file name attached to it.

So the final table would look like the following;

Var1 Var2 Period

2 323 2014_1

1 432 2014_1

4 432 2014_2

etc.

Can anyone help with this?

Thank you