BookmarkSubscribeRSS Feed
GAUTAMDVN
Calcite | Level 5

“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

9 REPLIES 9
PaigeMiller
Diamond | Level 26

Show us your code.

--
Paige Miller
GAUTAMDVN
Calcite | Level 5

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.

Reeza
Super User

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.


 

GAUTAMDVN
Calcite | Level 5

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 ;
Reeza
Super User

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 ;

 

GAUTAMDVN
Calcite | Level 5

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

Reeza
Super User

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


 

 

 

 

 

 

Reeza
Super User

@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


 

 

 

 

 

 


Screen Shot 2018-06-17 at 1.42.05 PM.pngScreen Shot 2018-06-17 at 1.42.22 PM.png

akki39k
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1061 views
  • 0 likes
  • 4 in conversation