topic Reading raw datafiles in SAS Programming

Reading raw datafiles

GAUTAMDVN — Sun, 17 Jun 2018 16:17:43 GMT

“HCA_file.txt” file contains different types of information for each Client_id, State, DoB in header row and different purchases carried out on different dates in transaction row. Import the data into SAS by retaining client_id and State such that i) Only transaction data is imported ii) Only header data is imported

Re: Reading raw datafiles

PaigeMiller — Sun, 17 Jun 2018 16:22:22 GMT

Show us your code.

Re: Reading raw datafiles

GAUTAMDVN — Sun, 17 Jun 2018 16:27:12 GMT

Hi Paige Miller,

I'm not able to find any relevant logic to write the statements to import the data into SAS because almost in every records there is different type of data but according to the query they've only ID, State, DOB and different purchases.

Re: Reading raw datafiles

Reeza — Sun, 17 Jun 2018 17:44:28 GMT

It’s a well formatted file. There are two record types, C and H which appear consistent in the example posted. Search conditional input if you have no idea of where to start.

@GAUTAMDVN wrote:

Hi Paige Miller,

I'm not able to find any relevant logic to write the statements to import the data into SAS because almost in every records there is different type of data but according to the query they've only ID, State, DOB and different purchases.

Re: Reading raw datafiles

GAUTAMDVN — Sun, 17 Jun 2018 19:18:41 GMT

Hi, please confirm why I'm wrong in the below code.

DATA X1;
INFILE HCA ;
INPUT ID $ @;
IF ID  = 'H' THEN INPUT CLIENT_ID STATE $ DOB : DATE7. ;
ELSE IF ID = 'C' THEN INPUT TRANSACTION_DATE : DATE7. PURCHASE : COMMA11.2;
FORMAT DOB transaction_date DATE9. PURCHASE DOLLAR11.2 ;
RUN ;

Re: Reading raw datafiles

Reeza — Sun, 17 Jun 2018 19:21:03 GMT

What makes you think its wrong in the first place? If you have errors in your log post the log.

@GAUTAMDVN wrote:

Hi, please confirm why I'm wrong in the below code.

DATA X1;
INFILE HCA ;
INPUT ID $ @;
IF ID  = 'H' THEN INPUT CLIENT_ID STATE $ DOB : DATE7. ;
ELSE IF ID = 'C' THEN INPUT TRANSACTION_DATE : DATE7. PURCHASE : COMMA11.2;
FORMAT DOB transaction_date DATE9. PURCHASE DOLLAR11.2 ;
RUN ;

Re: Reading raw datafiles

GAUTAMDVN — Sun, 17 Jun 2018 19:26:54 GMT

Hi,

The code is correct as per attachment but not able to import the data into SAS by retaining only client_id and State such that A. only transaction data is imported B. only header data is imported

Re: Reading raw datafiles

Reeza — Sun, 17 Jun 2018 19:31:00 GMT

The data is being read in correctly. You have not accounted for the fact that you want to retain the header information across the rows. You can look into RETAIN to hold values until you need to reset it. Please post your code and output directly into the forum rather than as attachments.

@GAUTAMDVN wrote:

Hi,

The code is correct as per attachment but not able to import the data into SAS by retaining only client_id and State such that A. only transaction data is imported B. only header data is imported

Re: Reading raw datafiles

Reeza — Sun, 17 Jun 2018 19:47:30 GMT

@Reeza wrote:

The data is being read in correctly. You have not accounted for the fact that you want to retain the header information across the rows. You can look into RETAIN to hold values until you need to reset it. Please post your code and output directly into the forum rather than as attachments.

@GAUTAMDVN wrote:

Hi,

The code is correct as per attachment but not able to import the data into SAS by retaining only client_id and State such that A. only transaction data is imported B. only header data is imported

Re: Reading raw datafiles

akki39k — Sun, 27 Jan 2019 12:15:13 GMT

This should do the job

data HCA_file (drop = fil);
infile " " ;
input fil :$1 @ ;
if fil = "H" then
input #1 @3 Id :$6. State :$4. ;
if fil = "C" then
input Dates :date9. Transaction :dollar6.2 ;

retain ID state Date;
if dates = . then delete;
format Dates date9.;
run;

data HCA_file (drop = fil);
infile " " ;
input fil :$1 @;
if fil = "H" then
input Id :$6. State :$4. DOB :date9.;
if fil ="C" then delete;
format dob date9.;
run;