BookmarkSubscribeRSS Feed
Louis_Gallo_CIGNA_com
Calcite | Level 5

(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

5 REPLIES 5
Reeza
Super User
Not a robust way, you can use proc import with DBMS = CSV but it guesses at data types and doesn't always guess correctly.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

FreelanceReinh
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1130 views
  • 1 like
  • 4 in conversation