topic Importing CSV Data - How do I account for changing field order? in SAS Data Management

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

Louis_Gallo_CIGNA_com — Mon, 30 Nov 2015 20:17:48 GMT

(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

'Time:Date'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

'Time:Date'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

'Time:Date'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

'Time:Date'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

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

FreelanceReinh — Mon, 30 Nov 2015 22:07:12 GMT

Have you checked the GETNAMES statement of PROC IMPORT?

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

Reeza — Tue, 01 Dec 2015 00:03:17 GMT

Not a robust way, you can use proc import with DBMS = CSV but it guesses at data types and doesn't always guess correctly.

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

RW9 — Tue, 01 Dec 2015 09:25:42 GMT

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.

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

FreelanceReinh — Tue, 01 Dec 2015 10:03:56 GMT

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

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

RW9 — Tue, 01 Dec 2015 11:24:34 GMT

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.