%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?
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.
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;
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
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.
You cannot use variable lists in SQL code. Only in SAS code, like your dataset option.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: