SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Importing CSV Data - How do I account for changing field order?

Reply
Occasional Contributor
Posts: 11

Importing CSV Data - How do I account for changing field order?

(Apologies if I am not in the right forum)

 

I am a SAS novice at best. I use something like this to import a csv file with server statistics. I used the import wizard in SE

 

DATA WORK.Server_Name_Exported;

 

LENGTH

  'TimeSmiley Very Happyate'n 8

  'Time:Time'n 8

  System $10

  Resource $5

  '%busy'n 8

  'reads/s'n 8

  'writes/s'n 8

  'r+w/s'n 8

  avserv 8

  avwait 8

  'KB write/s'n 8

 'KB read/s'n 8

  avque 8 ;

 

LABEL

  System ="SystemName" ;

 

FORMAT

  'TimeSmiley Very Happyate'n MMDDYY10.

  'Time:Time'n TIME8.

  System $CHAR10.

  Resource $CHAR5.

  '%busy'n BEST10.

  'reads/s'n BEST10.

  'writes/s'n BEST10.

  'r+w/s'n BEST10.

  avserv BEST10.

  avwait BEST10.

  'KB write/s'n BEST10.

  'KB read/s'n BEST10.

  avque BEST10. ;

  

INFORMAT

  'TimeSmiley Very Happyate'n MMDDYY10.

  'Time:Time'n TIME11.

  System $CHAR10.

  Resource $CHAR5.

  '%busy'n BEST10.

  'reads/s'n BEST10.

  'writes/s'n BEST10.

  'r+w/s'n BEST10.

  avserv BEST10.

  avwait BEST10.

  'KB write/s'n BEST10.

  'KB read/s'n BEST10.

  avque BEST10. ;

 

INFILE 'Exported_Linux_Device_Partition.txt'

  DELIMITER=','

  MISSOVER

  DSD

  FIRSTOBS=2 ;

 

INPUT

  'TimeSmiley Very Happyate'n : ?? MMDDYY8.

  'Time:Time'n : ?? TIME8.

  System : $CHAR10.

  Resource : $CHAR5.

  '%busy'n : ?? COMMA10.

  'reads/s'n : ?? COMMA10.

  'writes/s'n : ?? COMMA10.

  'r+w/s'n : ?? COMMA10.

  avserv : ?? COMMA10.

  avwait : ?? COMMA10.

  'KB write/s'n : ?? COMMA10.

  'KB read/s'n : ?? COMMA10.

  avque : ?? COMMA10. ;

 

RUN;

 

My issue that this works great for a format of CSV file where the field names are ALWAYS IN THE SAME ORDER.

 

Is there a way to import CSV data with field names, and not have to worry if the field order changes or if fields get added?

 

Any help is appreciated.

 

Thanks,

 

Lou

Trusted Advisor
Posts: 1,117

Re: Importing CSV Data - How do I account for changing field order?

Posted in reply to Louis_Gallo_CIGNA_com

Have you checked the GETNAMES statement of PROC IMPORT?

Super User
Posts: 19,770

Re: Importing CSV Data - How do I account for changing field order?

Posted in reply to Louis_Gallo_CIGNA_com
Not a robust way, you can use proc import with DBMS = CSV but it guesses at data types and doesn't always guess correctly.
Super User
Super User
Posts: 7,942

Re: Importing CSV Data - How do I account for changing field order?

Posted in reply to Louis_Gallo_CIGNA_com

Why would the strcuture of the file change?  The reason I ask is that you can't write fixed code based on a moving platform.  I would advise against the above suggestion of using proc import, it may fix this specific issue, but you will end up with a dataset where formats are different, or you don't have all the variables, or some additional, and then you will have to code around all of this.  My suggestion is that you go back to the source of the input data and ask them to provide a specific file with a given structure.  In an ideal world you would draw up an in import agreement which defines that strcuture, and both parties agree to it.  Then there is none of this messing about.  

Trusted Advisor
Posts: 1,117

Re: Importing CSV Data - How do I account for changing field order?

My understanding was (but I may be wrong) that those "server statistics" are not data which are put together by some (human) business partner, but are retrieved by a command, script or something. If this is the case, the structure of the data may change due to a version update of the underlying server software.

 

I agree that this is an unsatisfactory situation and that careful programming will be necessary to ensure a correct data import if something changes without prior notice (e.g. the date format from MMDDYY to YYMMDD).

Super User
Super User
Posts: 7,942

Re: Importing CSV Data - How do I account for changing field order?

Posted in reply to FreelanceReinhard

Ah, well then this process needs to be part of any change management on the server.  What I would say is that the program to read the data in should be fixed, per the current setup.  If the setup changes the program will fail and it will be picked up then, however, an ideal setup would be that this is part of the change management - so when the system is changed then this is also checked/validated.  At a quick google, there appear to be a fair few command line tools out there to provide stats on linux server, is the OP using something like that?  I wouldn't expect the output from such a tool to change, based on underlying system changes, so this wouldn't then impact.

Ask a Question
Discussion stats
  • 5 replies
  • 364 views
  • 1 like
  • 4 in conversation