topic Re: Proc Sql to import in SAS Programming

Proc Sql to import

abdulla — Wed, 28 Oct 2020 14:13:03 GMT

%let year1=1950; %let year2=2020;

proc sql; create table comp as
select *
from mydat.abc (keep=gvkey year IND--BLOCK)
where fyear between &year1 and &year2
order by gvkey, year;
quit;
RUN;

My above code works nicely. But I want to make slight changes like the following but it doesn't give me all the variables from IND to Block

%let year1=1950; %let year2=2020;

proc sql; create table comp as
select gvkey, year, IND--BLOCK
from mydat.abc
where fyear between &year1 and &year2
order by gvkey, year;
quit;
RUN;

Can anyone tell me how should I use IND--BLOCK and get all the variables between IND and BLOCK following the 2nd example?

Re: Proc Sql to import

novinosrin — Wed, 28 Oct 2020 14:19:22 GMT

Hi @abdulla Please try-

%let year1=1950; %let year2=2020;

proc sql; 
create table comp(keep=gvkey year IND--BLOCK) as
select *
from mydat.abc 
where fyear between &year1 and &year2
order by gvkey, year;
quit;
RUN;

/*OR why not in the FROM clause like your example1?*/

%let year1=1950; %let year2=2020;

proc sql; 
create table comp as
select *
from mydat.abc(keep=gvkey year IND--BLOCK)
where fyear between &year1 and &year2
order by gvkey, year;
quit;

Re: Proc Sql to import

abdulla — Wed, 28 Oct 2020 14:23:34 GMT

Hi novinosrin,
As I showed in my first code, it works. But I don't want to use select*. I want to use select gvkey, year, IND....... Because in my where statement I have the following

and CONSOL='C'
and INDFMT='INDL'
and DATAFMT='STD'
and POPsrc='D'

If I use the 2nd code, it shows that I don't have the above things

Re: Proc Sql to import

novinosrin — Wed, 28 Oct 2020 14:22:13 GMT

Okay I am afraid SQL clauses do not accept variable lists. You would have to use dataset options i.e Keep= or Drop= either in the FROM table or CREATE table.

Re: Proc Sql to import

Tom — Wed, 28 Oct 2020 14:23:10 GMT

You cannot use variable lists in SQL code. Only in SAS code, like your dataset option.