topic How to remove a variable from this proc export? in SAS Procedures

How to remove a variable from this proc export?

EinarRoed — Tue, 18 Sep 2012 07:38:36 GMT

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.

Re: How to remove a variable from this proc export?

Ksharp — Tue, 18 Sep 2012 08:46:46 GMT

You can drop it.

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

Ksharp

Re: How to remove a variable from this proc export?

EinarRoed — Tue, 18 Sep 2012 09:58:39 GMT

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.

Re: How to remove a variable from this proc export?

Ksharp — Wed, 19 Sep 2012 02:54:15 GMT

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

Re: How to remove a variable from this proc export?

art297 — Wed, 19 Sep 2012 12:59:02 GMT

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.

Re: How to remove a variable from this proc export?

jonam — Wed, 19 Sep 2012 18:39:17 GMT

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.

Re: How to remove a variable from this proc export?

art297 — Wed, 19 Sep 2012 18:50:25 GMT

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'.

Re: How to remove a variable from this proc export?

Tom — Wed, 19 Sep 2012 18:58:32 GMT

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;

Re: How to remove a variable from this proc export?

jonam — Thu, 20 Sep 2012 08:12:16 GMT

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

Re: How to remove a variable from this proc export?

Amir — Thu, 20 Sep 2012 11:57:16 GMT

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.

Re: How to remove a variable from this proc export?

art297 — Thu, 20 Sep 2012 13:03:48 GMT

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.

Re: How to remove a variable from this proc export?

robertrao — Fri, 21 Sep 2012 19:33:31 GMT

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

Re: How to remove a variable from this proc export?

Tom — Sat, 22 Sep 2012 02:06:35 GMT

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

Re: How to remove a variable from this proc export?

robertrao — Sun, 23 Sep 2012 01:51:14 GMT

Thanks Tom.

It was very detailed. I appreciate your time.