topic Import data that is long format and convert to wide format in SAS Procedures

Import data that is long format and convert to wide format

shellp55 — Fri, 30 Aug 2013 14:43:32 GMT

Hello

I am trying to import a .txt file where the data is "long" and I want to convert it to "wide" where one line of data is one record.

An example of one record of the raw data would be:

SHEPE

00001

89582

E12345

7/1/2012 10:24:05 AM

What I want it to look like:

SHEPE0000189582E123457/1/2012 10:24:05 AM103

Field description:

DocName = text

DocService = text

Chartno = text

Acctno = text

visit date/time = date/time

triage level = text

disposition code =text

Thanks for any assistance.

Re: Import data that is long format and convert to wide format

Tom — Fri, 30 Aug 2013 16:13:48 GMT

It sounds like you want to convert one form of raw data into another. Why?

I would suggest just reading it into SAS in its current form. Once you have read it you can write it out again in any form you want.

data want ;

input

DocName $30.

/ DocService $30.

/ Chartno $10.

/ Acctno $10.

/ VisitDT anydtdtm.

/ Triage $1.

/ Disposition $2.

;

format visitdt datetime20. ;

cards;

SHEPE

00001

89582

E12345

7/1/2012 10:24:05 AM

;

Re: Import data that is long format and convert to wide format

shellp55 — Sat, 31 Aug 2013 21:33:07 GMT

Sorry but your program copied and used as is into SAS doesn't work. It shows the data on two lines and blanks in some of the sections.

I want to convert the format of the raw data to better manipulate it for my needs i.e. linking to another table that is in the wide format.

Any assistance would be greatly appreciated. Thanks.

Re: Import data that is long format and convert to wide format

Tom — Sun, 01 Sep 2013 01:08:30 GMT

The program runs fine. Your actual data is probably more complex so you will need to adjust it to match your data.

It creates a dataset with multiple variables which is what it sounds like you need.

1 data want ;

2 input

3 DocName $30.

4 / DocService $30.

5 / Chartno $10.

6 / Acctno $10.

7 / VisitDT anydtdtm.

8 / Triage $1.

9 / Disposition $2.

10 ;

11 format visitdt datetime20. ;

12 cards;

NOTE: The data set WORK.WANT has 1 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.09 seconds

cpu time 0.05 seconds

20 ;

21 data _null_;

22 set;

23 put (_all_) (=/);

24 run;

DocName=SHEPE

DocService=00001

Chartno=89582

Acctno=E12345

VisitDT=01JUL2012:10:24:05

Triage=1

Disposition=03

NOTE: There were 1 observations read from the data set WORK.WANT.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds