BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vanmon1
Obsidian | Level 7

 

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 resultsCurrent resultsDesired result (last variable missing)Desired result (last variable missing)

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

10 REPLIES 10
vanmon1
Obsidian | Level 7

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.

 

Kurt_Bremser
Super User

@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;
vanmon1
Obsidian | Level 7

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

Kurt_Bremser
Super User

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.

vanmon1
Obsidian | Level 7

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

vanmon1
Obsidian | Level 7

Great! Perfect! Thanks very much.

data_null__
Jade | Level 19

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.

vanmon1
Obsidian | Level 7
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.

 

Tom
Super User Tom
Super User

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.

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
  • 10 replies
  • 1341 views
  • 1 like
  • 4 in conversation