topic Re: droping variables in export to multiple csv files with column headings in SAS Programming

droping variables in export to multiple csv files with column headings

vanmon1 — Mon, 23 Oct 2017 12:24:33 GMT

proc sort data=csv3;
by csvf;
run;


data _null_;
  set csv3;
  by csvf;
 
  fv = "\\tsclient\C\Transferencia de Archivos\EXPORTED FINAL TABLE\OPORTO\BASTIDORES\LINEA P" ||"_"|| TRIM(put(csvf,$50.)) || ".csv";

  file write filevar=FV dsd  dlm=','  lrecl=32000 ;
  if first.csvf then link names;
  put (_all_) (:) ;
return;

names:
length _name_ $50;
call missing(_name_);do while(1);
call vnext(_name_);
if _name_ eq: 'FIRST.' then leave;
put _name_@;
end;
put;

run;

Hello, I have managed to export my data set in to multiple csv files by each change in the last variable and with column headings. created this last variable to group and name each of those csv files but I would like to get rid of it inside the csv file. Is this possible?

TCurrent resultsDesired result (last variable missing)

Thank you in advance.

Re: droping variables in export to multiple csv files with column headings

Kurt_Bremser — Mon, 23 Oct 2017 12:28:53 GMT

Use the drop= dataset option in the set statement.

Re: droping variables in export to multiple csv files with column headings

vanmon1 — Mon, 23 Oct 2017 12:39:35 GMT

Hi KurtBremser,

I´m afraid that does not work because of the way I input the column names using vnext , I think.

Basically it took me a long time to add the column headings and now I cannot drop the variables I don´t need.

Re: droping variables in export to multiple csv files with column headings

Kurt_Bremser — Mon, 23 Oct 2017 12:52:31 GMT

@vanmon1 wrote:

Hi KurtBremser,

I´m afraid that does not work because of the way I input the column names using vnext , I think.

Basically it took me a long time to add the column headings and now I cannot drop the variables I don´t need.

Then you have to replace the _all_ with an exhaustive list of the variables you want output, and do similar for the header.

You could facilitate that by creating a list of variables from dictionary.columns before you add the filename variable, and then use that list to write dynamic code:

proc sql noprint;
select name into :varlist1 separated by ' ' from dictionary.columns where libname = 'WORK' and memame = 'CSV3';
select name into :varlist2 separated by ',' from dictionary.columns where libname = 'WORK' and memame = 'CSV3';
quit;

/* add csvf here */

data _null_;
set csv3;
by csvf;
fv = "\\tsclient\C\Transferencia de Archivos\EXPORTED FINAL TABLE\OPORTO\BASTIDORES\LINEA P" ||"_"|| TRIM(put(csvf,$50.)) || ".csv";
file write filevar=fv dsd dlm=',' lrecl=32000;
if first.csvf then put "&varlist2";
put &varlist1;
run;

Re: droping variables in export to multiple csv files with column headings

vanmon1 — Mon, 23 Oct 2017 14:02:01 GMT

Hi again,

I get no rows selected when using the dictionary.columns , also I don´t understand what you mean with the /*add csvf here' comment*/ Thanks

2266  proc sql noprint;
2267  select name into :varlist1 separated by ' ' from dictionary.columns where libname =
2267! 'WORK' and memname='csv3';
NOTE: No rows were selected.
2268  select name into :varlist2 separated by ',' from dictionary.columns where libname =
2268! 'WORK' and memname='csv3';
NOTE: No rows were selected.
2269  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: droping variables in export to multiple csv files with column headings

Kurt_Bremser — Mon, 23 Oct 2017 14:08:13 GMT

Always write memnames in capitals.

And my comment is the placeholder for "created this last variable to group and name each of those csv files" from your initial post.

If that variable is there from the beginning, add a suitable condition:

proc sql noprint;
select name into :varlist1 separated by ' ' from dictionary.columns where libname = 'WORK' and memname = 'CSV3' and upcase(name) ne 'CSVF';
select name into :varlist2 separated by ',' from dictionary.columns where libname = 'WORK' and memname = 'CSV3' and upcase(name) ne 'CSVF';
quit;

While libnames and memnames are always supplied by SAS in capitals, variable names retain their case.

Re: droping variables in export to multiple csv files with column headings

vanmon1 — Mon, 23 Oct 2017 14:19:33 GMT

Now it Works! Thanks very much for your time and patience!

Re: droping variables in export to multiple csv files with column headings

vanmon1 — Mon, 23 Oct 2017 14:20:51 GMT

Great! Perfect! Thanks very much.

Re: droping variables in export to multiple csv files with column headings

data_null__ — Mon, 23 Oct 2017 14:37:29 GMT

CSVF is the filename and looks like it was created from values of existing variables . If so you can move the code that creates that string to the DATA _NULL_ step and include in the creation of FV variable. Notice that since FV is the FILEVAR is not included in _ALL_.

Show the code that makes CSVF.

Re: droping variables in export to multiple csv files with column headings

vanmon1 — Tue, 24 Oct 2017 07:23:48 GMT

PROC SQL;
CREATE TABLE FINAL AS 
SELECT 
A.ESTACION as ESTACION, A.LEVEL, A.STATUS, A.VENDOR_CODE, B.PART AS ITEMNUM, A.MOD_LEVEL, A.SERIALNUM, A.DESCRIPTION, A.DERIVED_POSITION, A.BTSUPPLIEREXPDATE,A.PURCHASE_ORDER,A.MANUFACTURER_CODE, 
A.WARRCHECKDATE, a.orden
FROM B15 A JOIN CMITEM3 B ON A.DESCRIPTION=B.DESCITEM;
QUIT;
data csv;
set final;
ocb=substr(serialnum,11,4);
oc=substr(serialnum,11,3);
run;
proc sql;
create table csv2 as select a.*, case when oc like '%%H%%' then a.estacion||b.ocb end as csvf
from final a join csv b on a.serialnum=b.serialnum and a.estacion=b.estacion ;
quit;

data csv3;
   set csv2;
   if orden=2 then comodin=csvf;
   retain comodin;
   if missing(csvf) then csvf= comodin;
   else comodin=csvf;
   drop comodin orden;
run;

proc sort data=csv3;
by csvf;
run;


proc sql noprint; 
select name into :varlist1 separated by ' ' from dictionary.columns where libname = 'WORK' and memname='CSV3' and upcase(name) not in ( 'CSVF','ESTACION');
select name into :varlist2 separated by ',' from dictionary.columns where libname = 'WORK' and memname='CSV3' and upcase(name) not in  ('CSVF','ESTACION');
quit;



data _null_;
set csv3;
by csvf;
fv = "\\tsclient\C\Transferencia de Archivos\EXPORTED FINAL TABLE\OPORTO\BASTIDORES\LINEA P" ||"_"|| TRIM(put(csvf,$50.)) || ".csv";
file write filevar=fv dsd dlm=',' lrecl=32000;
if first.csvf then put "&varlist2";
put &varlist1;
run;

Hi data _null_ , actually I read some of your posts and found them very useful. Above is my code. Always interesting to learn new ways. Thanks.

Re: droping variables in export to multiple csv files with column headings

Tom — Tue, 24 Oct 2017 21:56:08 GMT

If you don't want to print the variable then do not include it in the input dataset. Looks like you are using it to create the variable used on the FILE statement. So create and keep that variable instead.

Here is an example using SASHELP.CLASS and using NAME as the grouping variable for generating the filename, but dropping it from the data file.

%let prefix=c:\downloads\example ;

data toprint ;
  set sashelp.class(obs=3);
  by name ;
  length fv $200 ;
  fv = cats("&prefix._",name,'.csv') ;
  drop name ;
run;

data _null_;
  set toprint ;
  by fv;
  file write filevar=FV lrecl=32000 dsd  dlm=','  ;
  if first.fv then link names;
  put (_all_) (:) ;
return;
names:
 length _name_ $32;
 call missing(_name_);
 do while(1);
   call vnext(_name_);
   if _name_ eq: 'FIRST.' then leave;
   if upcase(_name_) ne 'FV' then put _name_ @;
 end;
 put;
return;
run;

Note that I had to add extra logic in the subroutine that prints the column headers to prevent it from writing a header for the FV variable. Here you can see the resulting three files that are created.

1324  data _null_;
1325    infile "&prefix._*.csv";
1326    input;
1327    put _infile_;
1328  run;

NOTE: The infile "c:\downloads\example_*.csv" is:
      Filename=c:\downloads\example_Alfred.csv,
      File List=c:\downloads\example_*.csv,RECFM=V,
      LRECL=32767

Sex,Age,Height,Weight
M,14,69,112.5
NOTE: The infile "c:\downloads\example_*.csv" is:
      Filename=c:\downloads\example_Alice.csv,
      File List=c:\downloads\example_*.csv,RECFM=V,
      LRECL=32767

Sex,Age,Height,Weight
F,13,56.5,84
NOTE: The infile "c:\downloads\example_*.csv" is:
      Filename=c:\downloads\example_Barbara.csv,
      File List=c:\downloads\example_*.csv,RECFM=V,
      LRECL=32767

Sex,Age,Height,Weight
F,13,65.3,98
NOTE: 2 records were read from the infile "c:\downloads\example_*.csv".
      The minimum record length was 13.
      The maximum record length was 21.
NOTE: 2 records were read from the infile "c:\downloads\example_*.csv".
      The minimum record length was 12.
      The maximum record length was 21.
NOTE: 2 records were read from the infile "c:\downloads\example_*.csv".
      The minimum record length was 12.
      The maximum record length was 21.