BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Good Morning Everyone,

 

I have an existing process and an existing CSV file format by which I am constrained.  I have no control over either and am required to undertake this task within those constraints and can not manually manipulate the file before consumption.  I have researched the matter, however have been unable to locate a suitable solution.  I also do not have access to Powershell or Python which seems to handle this task well.

 

So, now that the caveat is out of the way:

 

I have a text file which is separated by commas.  This file arrives from a system on a monthly basis and the structure aligns with the agreements put in place some time ago (once again I have no ability to alter this agreement).  The file arrives with a file header spanning 5 rows and a column header on the 6th.  The current process is to pick up CSV files and import the column headers using PROC IMPORT.  The data contained within the column header is then compared against the records within a control table.  If the column names appear in both the CSV and the control table then the CSV is read in using the INFILE statement method (otherwise an email is sent requesting the new columns be mapped in the control table).  Obviously the problem using the PROC IMPORT method is that it expects the column names to appear in the first row, which they don't in this instance.  So to get past this constraint I decided to read the CSV in using the INFILE statement with a FIRSTOBS = 4 option and output the results to a temporary CSV file using the FILE statement, then consume the temporary Dataset using PROC IMPORT.  The following code is what I have written:

 

		FILENAME IN  "&FILEPATH.\&FILENAME.";
		FILENAME OUT "&FILEPATH.\TEMP_&FILENAME.";

                DATA _TEST_;
 		      INFILE IN FIRSTOBS = 6 LRECL = 32767;
       		      FILE OUT LRECL = 32767;
      		      INPUT;
       		      PUT _INFILE_;
     	        RUN;

The syntax appears sounds and I have seen similar suggestions posted by @Ksharp to deal with similar issues, however I end up with a blank temporary file and the following notes in the log.

 

NOTE: 0 records were written to the file OUT.
NOTE: 0 records were read from the infile IN.

 

Common solutions I have found in regards to PROC IMPORT reference the DATAROW and NAMEROW options, however these do not function for text files., 

 

Environment details - SAS 9.4M4, Windows Server 2012.

 

Any suggestions on how to resolve this matter would be greatly appreciated.

 

Regards,

Scott

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

So you want to read the column headers from row 6?

Why are you using PROC IMPORT for that step?

data columns;
  infile 'myfile.csv' dsd firstobs=6 obs=6 ;
  length column 8 name $32 ;
  column +1;
  input name @@ ;
run;

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

So you want to read the column headers from row 6?

Why are you using PROC IMPORT for that step?

data columns;
  infile 'myfile.csv' dsd firstobs=6 obs=6 ;
  length column 8 name $32 ;
  column +1;
  input name @@ ;
run;
Scott_Mitchell
Quartz | Level 8

Hi Tom,

 

Thank you for your reply.

 

As I said there are current routines (macros) in place that are employed to read in CSV's.  The first step of these routines is a PROC IMPORT to read in the first row (because all data thus far had the first row containing column headers).  I have no control of these macros, however given the elegance of your solution I may be able to have those in charge of maintaining them change their minds.

 

Thank you for your help.

 

Regards,

Scott 

Reeza
Super User

If the file format changes constantly PROC IMPORT is a good solution, if its a fixed format all the time then you can use a data step solution that's more robust. 

Scott_Mitchell
Quartz | Level 8

You are right Reeza and that is 100% why it was used in this context.

 

We have no control over the file structure and it tends to change at a whim.  I think Tom's solution still allows me to account for the fluidity.

ballardw
Super User

@Scott_Mitchell wrote:

You are right Reeza and that is 100% why it was used in this context.

 

We have no control over the file structure and it tends to change at a whim.  I think Tom's solution still allows me to account for the fluidity.


That sounds like who ever negotiated or set up the file "structure" agreement long ago needs lashes from wet spaghetti noodles...

 

 

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
  • 5 replies
  • 1789 views
  • 0 likes
  • 4 in conversation