DATA Step, Macro, Functions and more

droping variables in export to multiple csv files with column headings

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

droping variables in export to multiple csv files with column headings

 

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?

Timage.pngCurrent resultsimage.pngDesired result (last variable missing)

Thank you in advance.


Accepted Solutions
Solution
‎10-23-2017 10:18 AM
Super User
Posts: 9,932

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,932

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

Use the drop= dataset option in the set statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

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

Posted in reply to KurtBremser

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.

 

Super User
Posts: 9,932

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


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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

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

Posted in reply to KurtBremser

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

Solution
‎10-23-2017 10:18 AM
Super User
Posts: 9,932

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

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

Posted in reply to KurtBremser

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

Contributor
Posts: 23

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

Posted in reply to KurtBremser

Great! Perfect! Thanks very much.

Respected Advisor
Posts: 3,847

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

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.

Contributor
Posts: 23

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

Posted in reply to data_null__
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.

 

Super User
Super User
Posts: 7,944

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

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 154 views
  • 1 like
  • 4 in conversation