topic Re: select columns by skiping certain column in proc import in SAS Programming

select columns by skiping certain column in proc import

changxuosu — Mon, 01 Jul 2019 15:32:02 GMT

Hi SAS fellows,

Say I have an excel with data in cell A1 to Z100.

If I want to import only column A-B, and D-Z

How can I skip column C?

would anyone know some code like this will work?

proc import datafile="&econ_locat.&file." out=econfile dbms=xlsx replace;

range="Sheet1$A1:B100 and Sheet1$D1:Z100";

run;

Can anyone help me to edit the code "range="Sheet1$A1:B100 and Sheet1$D1:Z100;" to make it work?

Or other code that would work except that I don't want to use the "Keep = A B D E F G.... Z" option or "drop = C" option because the column name of C has spaces (it's "Real GDP" so I want to avoid using keep= and drop = options.
(because in excel the variable name has spaces, which brings another problem to solve)

(I don't want to change it manually in excel either, because there are actually hundreds of variables like this have spaces)

Thanks!!!!

Re: select columns by skiping certain column in proc import

Tommy1 — Mon, 01 Jul 2019 14:01:25 GMT

I have not tested it, but I assume if the (keep= a b d..) option is available then you could also do (drop = c))

Re: select columns by skiping certain column in proc import

changxuosu — Mon, 01 Jul 2019 15:17:39 GMT

thanks!!!

Re: select columns by skiping certain column in proc import

changxuosu — Mon, 01 Jul 2019 15:25:53 GMT

the problem is that my name has spaces so I want to avoid using keep= and drop = options.
(because in excel the variable name has spaces, which brings another problem to solve)

Re: select columns by skiping certain column in proc import

Tom — Mon, 01 Jul 2019 15:25:54 GMT

Does PROC IMPORT allow you to specify a compound range in the RANGE option? Try using the syntax that works in Excel.

range="Sheet1$A1:B100,Sheet1$D1:Z100";

But why not just drop it from the SAS dataset.

Do you know the name of the variable that is in the column you don't want?

out=econfile(drop=C)

Re: select columns by skiping certain column in proc import

changxuosu — Mon, 01 Jul 2019 15:29:14 GMT

thanks!!! I just tried range="Sheet1$A1:B100,Sheet1$D1:Z100"; no luck : (

Re: select columns by skiping certain column in proc import

Tommy1 — Mon, 01 Jul 2019 15:29:38 GMT

Yea I agree with @Tom , why not just bring everything in and then you can always write a quick data step and drop the variable. It not like there is a lot of data here that we need to worry about bringing too much in.