I want to pull out data by SAS from database.
Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below:
name birthday month
green xxxx 1
white xxxx 2
brown xxxx 3
black xxxx 4
and so on
Now I want to use the name as one criterion for another data pulling.
My code will be:
proc sql;
connect to xxxx(database);
create table want
as select * from connection to database
(select a.name, a.occupation, a.employer....
from surveydet.list
where (a.occupation in xxxxxxxxxx)
and (a.employer in xxxxxxxxxxx)
AND (A.NAME= LIST.NAME);
disconnect from databse;
quit;
The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.
Thanks,
Given what you've shown here, you can't do that because you're using pass through SQL not PROC SQL. This means you're passing the query to the DB so all the data has to be on the data base, including your LIST table.
If it is you can just do:
where x in (Select name from list) and occupation in (select .... )
It's not quite clear if you want to do it for each name, or for all names at once.
Assuming all names at once you can create a macro variable with the list of names, if it's less than 64K characters in total.
Then it becomes:
where x in (&name_list)
I think you'll need to post some more details, your question as it stands is quite vague so it's hard to give specific solutions.
If this is related to your query from yesterday then CALL EXECUTE is still the best method, IMO.
@leonzheng wrote:
I want to pull out data by SAS from database.
Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below:
name birthday month
green xxxx 1
white xxxx 2
brown xxxx 3
black xxxx 4
and so on
Now I want to use the name as one criterion for another data pulling.
My code will be:
proc sql;
connect to xxxx(database);
create table want
as select * from connection to database
(select a.name, a.occupation, a.employer....
from surveydet.list
where (a.occupation in xxxxxxxxxx)
and (a.employer in xxxxxxxxxxx)
AND (A.NAME= LIST.NAME);
disconnect from databse;
quit;
The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.
Thanks,
sorry, in the example, I only list 1 person for each month.
Then wrapping it in a macro and using CALL EXECUTE is your easiest choice.
Given what you've shown here, you can't do that because you're using pass through SQL not PROC SQL. This means you're passing the query to the DB so all the data has to be on the data base, including your LIST table.
If it is you can just do:
where x in (Select name from list) and occupation in (select .... )
It's not quite clear if you want to do it for each name, or for all names at once.
Assuming all names at once you can create a macro variable with the list of names, if it's less than 64K characters in total.
Then it becomes:
where x in (&name_list)
I think you'll need to post some more details, your question as it stands is quite vague so it's hard to give specific solutions.
If this is related to your query from yesterday then CALL EXECUTE is still the best method, IMO.
@leonzheng wrote:
I want to pull out data by SAS from database.
Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below:
name birthday month
green xxxx 1
white xxxx 2
brown xxxx 3
black xxxx 4
and so on
Now I want to use the name as one criterion for another data pulling.
My code will be:
proc sql;
connect to xxxx(database);
create table want
as select * from connection to database
(select a.name, a.occupation, a.employer....
from surveydet.list
where (a.occupation in xxxxxxxxxx)
and (a.employer in xxxxxxxxxxx)
AND (A.NAME= LIST.NAME);
disconnect from databse;
quit;
The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.
Thanks,
Thank you, my real case is much more complicated to describe and contain company information. So I tried to use simple examples to describe my question. I think your suggestion is good. I will try to form a list file and get selection information from it with macro.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.