How to remove a variable from this proc export?

[EinarRoed]

The following code creates a .csv-document that contains all the variables of the &_input table. This is such a newbie question, but what can I do to exclude variables from the table? In example, I want to export the input data set without a variable called "w_code".

%let dsnid = %sysfunc(open(&_input));

%let nobs=0;

%if &dsnid %then %do;

     %let nobs=%sysfunc(attrn(&dsnid,nlobs));

     %let rc=%sysfunc(close(&dsnid));

%end;

%put "&nobs";

%if &nobs > 0 %then %do;

     proc export data=&_input.

     outfile="&filsti"

     label dbms=dlm replace;

     delimiter='3B'x; *3B=Semicolon;

     run;

%end;

Thanks for your time.

[Ksharp]

You can drop it.

proc export data=&_input.(drop=w_code)

Ksharp

[EinarRoed]

For some reason, when I do that, the w_code rows become empty yet the w_code header remains. Vice versa if I try to "keep" columns.

[Ksharp]

Sorry . I don't realize there would be such problem. I don't know why proc export can't drop these variables.

But since it all be done , maybe you need to process dataset before export it.

data class;
set sashelp.class(drop=name sex);
run;

proc export data=class outfile='c:\temp\x.txt' 
replace dbms=dlm; delimiter='|'; putnames=no;
run;

Ksharp

[art297]

It is a bug with proc export of delimited files.  The functionality should be there .. it is when exporting an Excel workbook.

A work around would be to get the code that proc export created in the background (i.e., press function key F4 after you run the code), and then delete all of the references to the variable(s) you want to drop.  You would have to drop them from the variable name declarations, the format declarations and the actual put statements.

I'd also recommend opening a track with Tech Support as this is clearly a bug/omission of a documented feature and should be fixed.

[jonam]

164  proc export data=class(drop=sex  age) outfile='c:\temp\x.txt'

165  replace dbms=dlm;

165!                   delimiter='|';

166  run;

WARNING: This DBMS type does not support Data Set options. The options entered will be ignored.

I got the following warning when i tried. It's not dropping variables due to "This dbms type does not support data set options".  It's not a bug i guess.  They aware of this.

But as Art mentioned,  May be the Tech support can give a work around for this if you raise a ticket.

[art297]

I don't know which version of SAS you are using.  On 9.2, it runs just fine, except for the fact that it keeps the variable's format statements, keeps the variable names in the list, and includes extra tabs for the dropped variables in the output.

As such, my log read:

NOTE: Variable Sex is uninitialized.

NOTE: Variable Age is uninitialized.

NOTE: The file 'c:\art\x.txt' is:

      Filename=c:\art\x.txt,

      RECFM=V,LRECL=32767,File Size (bytes)=0,

      Last Modified=19Sep2012:14:47:40,

      Create Time=10Dec2011:18:46:31

NOTE: 20 records were written to the file 'c:\art\x.txt'.

[jonam]

I used SAS 9.3. may be that's why i'm getting this message.

[Amir]

Hi,

I tried running just the code in your post, using SAS 9.1.3 and received the same warning message about "This DBMS type does not support Data Set options."

Looking closer at the code I realised the data set was specified as "class", so I changed it to "sashelp.class" then it worked ok.

Regards,

Amir.

[art297]

Jonam: I think Amir is correct.  I don't get the note you mentioned on 9.3 either .. just the same bug as found in 9.2.

[robertrao]

Hi Team,

I am curious to know about the &_input...............This is a dataset name???????????

I learnt few days ago file names start with an underscore.

Why is the underscore used infront of the dataset here????can i use it like _&input?????????

Also confirm:

libname test "c:\temp\forum\tete.xls";

%macro test;          

%do i=1 %to &n;

%let dsn=&&id&i;

data test._&dsn;                                                         /*how is that this datastep is creating a file and not a dataset??????*/                            

   set sashelp.class(where=(sex="&dsn"));

run;                                  

%end;

%mend test;

%test

libname test clear;

Great Help

Thanks

[Tom]

To your first point the macro variable name in the originally posted code was _INPUT.  So &_INPUT is correct.  Some users have a naming convention of using _ prefix on local macro variables to make it easier for humans to make an educated guess about the type of macro variable.  The value of the macro variable _INPUT will need to be a valid dataset name for the program to work.

To your second point it is really the LIBNAME statement that is creating the .XLS file.  The programmer left off the engine specification in the LIBNAME statement, but SAS is pretty good at guessing what you mean and so will use the EXCEL engine when you point a libname to a file with .XLS extension instead of a directory.

1    libname test1 'c:\temp\test1.xls';

NOTE: Libref TEST1 was successfully assigned as follows:

      Engine:        EXCEL

      Physical Name: c:\temp\test1.xls

2    libname test2 'c:\temp';

NOTE: Libref TEST2 was successfully assigned as follows:

      Engine:        V9

      Physical Name: c:\temp

[robertrao]

Thanks Tom.

It was very detailed. I appreciate your time.

[Tom · Accepted solution]

There is clearly a bug in PROC EXPORT as it is getting the list of variable names without honoring the DROP option. But then in the generated code it does include the DROP option.

You can create view to work around this.

proc sql ;

  create view export as select * from sashelp.class(drop=sex);

quit;

proc export data=export

outfile=tmpfile1 replace dbms=dlm;

delimiter='|';

run;