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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

13 REPLIES 13
Ksharp
Super User

You can drop it.

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

Ksharp

EinarRoed
Pyrite | Level 9

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
Super User

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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

Amir
PROC Star

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
Opal | Level 21

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
Quartz | Level 8

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
Super User Tom
Super User

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
Quartz | Level 8

Thanks Tom.

It was very detailed. I appreciate your time.

Tom
Super User Tom
Super User

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 13824 views
  • 7 likes
  • 7 in conversation