BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

Appreciate if someone guide me with the program to import .txt file which has records as follows.

 

Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/sasuserl 1419.00 503.79 65% 48937 1% /sasuser

 

After import, I want to export to .csv as follows.

 

Filesystem  GB blocks Free %Used Iused  %Iused Mounted on
/dev/sasuserl 1419.00 503.79 65% 48937% 1% /sasuser

 

Thanks for any help you offer me.

6 REPLIES 6
Kurt_Bremser
Super User

Use list input without the DSD option to read; use list output with DLM=',' to create the csv file.

For the percent values, use the PERCENTw.d format to read and write.

All in all a very simple task.

Patrick
Opal | Level 21

Both .csv and .txt are text files. They just have different delimiters. So in your case you could simply read the source text file into the input buffer, exchange the source delimiter with a comma and then write the result straight back to the output file. There is no need to map the data to SAS variables and input source strings into SAS numeric variables.

 

 

/* create source text file with delimiter '|' (tab or whatever in your real data) */
filename have temp;
data _null_;
file have;
infile datalines;
input;
put _infile_;
datalines;
Filesystem|GB blocks|Free|%Used|Iused|%Iused|Mounted on
/dev/sasuserl|1419.00|503.79|65%|48937|1%|/sasuser
;
run;

/* read source file "want" into input buffer and replace the source file delimiter (tab?) with comma */
filename want temp;
data _null_;
file want;
infile have;
input;
_infile_=translate(_infile_,',','|');
put _infile_;
/* putlog just here to see in sample code what gets written to file "want" */
putlog _infile_;
run;

 

And in case your source text file doesn't have a delimiter and data is just written out positional with blanks then the following code version could deal with that.

/* create source text file */
filename have temp;
data _null_;
  file have;
  infile datalines;
  input;
  put _infile_;
  datalines;
Filesystem    GB blocks Free   %Used Iused %Iused Mounted on
/dev/sasuserl 1419.00   503.79 65%   48937 1%     /sasuser
;
run;

/* read source file "want" into input buffer and replace selected blanks with comma */
filename want temp;
data _null_;
  file want;
  infile have;
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
  /* putlog just here to see in sample code what gets written to file "want" */
  putlog _infile_;
run;

Babloo
Rhodochrosite | Level 12

Thank you for quick response. However, I'm unable to view the file (#LN00019) which was created in second data step.See the log below.  I'm in a need output .csv file after reading the input .txt file. Also in your first data step, you've hard coded the values which was in input .txt. Could you please tell me how to accomplish this task by reading the .txt file without hardcoding the values in it? 

 

NOTE: The file WANT is:
      Filename=/saswork/v9.4/SAS_work97110001A_ksnsnk02/#LN00019,
      Owner Name=rxxx,Group Name=sas,
      Access Permission=-rw-rw-r--,
      Last Modified=13Nov2016:14:50:55

NOTE: The infile HAVE is:
      Filename=/saswork/v9.4/SAS_work97110001A_ksnsnk02/#LN00018,
      Owner Name=rxxx,Group Name=sas,
      Access Permission=-rw-rw-r--,
      Last Modified=13Nov2016:14:50:55,
      File Size (bytes)=162

 

Patrick
Opal | Level 21

I'm unable to view the file (#LN00019) which was created in second data step

 

That's why I've also used "putlog" so you can see the output in the log.

 

For your real code you want of course to write the file to a permanent location and not to WORK so your code should look like:

filename have '<input path>/<name of source text file>';
filename want '<output path>/<name of target csv file>';
data _null_;
  file want;
  infile have;
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
run;
filename have clear;
filename want clear;

 

Patrick
Opal | Level 21

Or even this way:

data _null_;
  file '<output path>/<name of target csv file>';
  infile '<input path>/<name of source text file>';
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
run;

 

BTW: If you're the one creating the initial report via Unix command then you could of course also pipe the result directly to a RegEx and create the .csv directly without any SAS involved.

Shmuel
Garnet | Level 18

The most simple code to convert text file (blank delimiter) into csv (comma delimiter) is by:

 

data _null_;

       length a_row $100;

       infile '... input path and filename.txt' truncover;

       input a_row $;

       a_row = translate(compbl(a_row), ',' , ' ');

       a_row = compress(a_row, '%');

       file '... output path and filename.csv';

       put a_row ;

run;

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
  • 6 replies
  • 3349 views
  • 3 likes
  • 4 in conversation