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.
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 25. 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.