topic Re: Group Id and transpose in SAS Programming

Group Id and transpose

cxyu1 — Thu, 08 Aug 2019 22:12:52 GMT

have the following raw data set, and based on my understanding of "transpose" feature I need to first create some kind of group before I can transpose the data.

Raw Data

Field Value

Column1 ABC

Column2 DEF

Column3 HIJ

Column1 123

Column2 456

Column3 789

There are 3 columns or filed names.

I think I need to create a group Id first before I can use the transpose feature.

GroupID Field Value

1 Column1 ABC

1 Column2 DEF

1 Column3 HIJ

2 Column1 123

2 Column2 456

2 Column3 789

Here is my desired outcome...

Column1 Column 2 Column3

ABC DEF HIJ

123 456 789

Thanks in advance for any help or guidance/ aadvice.

Re: Group Id and transpose

Reeza — Thu, 08 Aug 2019 22:16:35 GMT

Is your file a text file? Is it just a single row of data? I'm worried about a solution provided not scaling to your actual problem.

Re: Group Id and transpose

cxyu1 — Thu, 08 Aug 2019 22:22:28 GMT

Its a text file, and contains less than 100 records, and less than 10 column/fields.

Re: Group Id and transpose

Reeza — Thu, 08 Aug 2019 22:24:11 GMT

Can you show a small sample of how the text file is structured. I don't know if column1 is actually the first column or values in a row?

Re: Group Id and transpose

cxyu1 — Thu, 08 Aug 2019 22:30:03 GMT

Here is the text data, so this would be 2 records after transposed

Cost Center	0300/0025812
Initiator	cxyu1
Method	ABC
Date Wire Initiated	8/6/2019
Value Date	8/6/2019
Time Wire Initiated	7:29:17
Committed Time of Wire	8:23:49
Reference Number	1.90806E+11
Account Number	XXXXX456
Amount	41,949.42
Cost Center	0300/0025812
Initiator	cxyu1
Method	ABC
Date Wire Initiated	8/6/2019
Value Date	8/6/2019
Time Wire Initiated	8:03:03
Committed Time of Wire	8:29:11
Reference Number	1.90806E+11
Account Number	XXXXXXX12
Amount	1,230.50

Re: Group Id and transpose

Reeza — Thu, 08 Aug 2019 22:35:20 GMT

Are you guaranteed to have every single value repeated?

If so, I highly recommend using a data step to import the file from a text file. Your code would end up looking like:

data want;
infile 'path to txt';
input #1 CostCenter $
#2 Intiator $
#3 Method
#4 Date_Wire_Initiated
#5 Date_Value
....
#10 Amount
;

run;

It's documented here:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a002001051.htm

This way you can control the types a bit more easily. I'm assuming you're familiar with a standard data step import and can modify this to fit your data via formats/informats or lengths.

Re: Group Id and transpose

cxyu1 — Thu, 08 Aug 2019 22:42:23 GMT

Yes, the columns fields will always be the same. I am a newbie so all I really did thus far was import the data using the data import wizard and tried the transpose data task. So this is a bit advanced for me.

When I used the transpose data task, that's where I for that I needed a group ID.

Re: Group Id and transpose

novinosrin — Thu, 08 Aug 2019 23:34:48 GMT

Hi @cxyu1 Import using datastep like this if you like, and then you can proceed

/*If your variable name patterns are consisten as in your sample*/
data have;
infile cards;
input ;
call scan(_infile_, -1, _p, _,' ');
var_names=substr(_infile_,1,_p-1);
var_values=substr(_infile_,_p);
if var_names='Cost Center' then grp+1;
drop _:;
cards;
Cost Center	0300/0025812
Initiator	cxyu1
Method	ABC
Date Wire Initiated	8/6/2019
Value Date	8/6/2019
Time Wire Initiated	7:29:17
Committed Time of Wire	8:23:49
Reference Number	1.90806E+11
Account Number	XXXXX456
Amount	41,949.42
Cost Center	0300/0025812
Initiator	cxyu1
Method	ABC
Date Wire Initiated	8/6/2019
Value Date	8/6/2019
Time Wire Initiated	8:03:03
Committed Time of Wire	8:29:11
Reference Number	1.90806E+11
Account Number	XXXXXXX12
Amount	1,230.50
;


proc transpose data= have out=want(drop=grp _name_);
by grp;
var var_values;
id var_names;
run;

Re: Group Id and transpose

ballardw — Thu, 08 Aug 2019 23:28:46 GMT

@cxyu1 wrote:

Yes, the columns fields will always be the same. I am a newbie so all I really did thus far was import the data using the data import wizard and tried the transpose data task. So this is a bit advanced for me.

When I used the transpose data task, that's where I for that I needed a group ID.

If you read data properly, such as with @Reeza's example, then you avoid the whole issue. Additionally some of your values look like they should be numeric, dates, times which will take yet another step to straighten out.

Plus your "reference number" value of 1.90806E+11 may have been corrupted by Excel (at a fairly well educated guess). Typically long identifiers should be read as character because of precision of numeric storage and many ID systems actually have significant leading zeroes which get lost in conversion to numeric. If that is how the values appear from Proc Import than that is almost certainly not what you want.

Proc import is best when used for tabular data where each column has the same type of data for each value not the mixed stuff you show.

Re: Group Id and transpose

cxyu1 — Fri, 09 Aug 2019 04:32:24 GMT

Instead of the ‘infile card’ function can I direct the program to an existing work query and then do the group ID assignment form there. I have friend different proc statements however no success.

Re: Group Id and transpose

novinosrin — Fri, 09 Aug 2019 11:45:48 GMT

@cxyu1 Infile cards that I used is merely a demonstration that gives you an idea.So yes, the idea is to use a datastep to import your raw data into sas dataset using infile and input precisely list input.

For your raw txt file, the code could like like

infile 'raw_file' ;

input

----------- and so on