DATA Step, Macro, Functions and more

Importing CSV Files From Record N

Accepted Solution Solved
Reply
Super Contributor
Posts: 310
Accepted Solution

Importing CSV Files From Record N

[ Edited ]

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


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,932

Re: Importing CSV Files From Record

[ Edited ]
Posted in reply to Scott_Mitchell

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


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 7,932

Re: Importing CSV Files From Record

[ Edited ]
Posted in reply to Scott_Mitchell

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;
Super Contributor
Posts: 310

Re: Importing CSV Files From Record N

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 

Super User
Posts: 23,237

Re: Importing CSV Files From Record N

Posted in reply to Scott_Mitchell

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. 

Super Contributor
Posts: 310

Re: Importing CSV Files From Record N

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.

Super User
Posts: 13,293

Re: Importing CSV Files From Record N

Posted in reply to Scott_Mitchell

@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...

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 165 views
  • 0 likes
  • 4 in conversation