Your SAS programs, embedded in web apps and elsewhere

sql query conversion to proc sql

Reply
Occasional Contributor
Posts: 5

sql query conversion to proc sql

need help to convert  below sql query into proc sql or data set query of sas 


Select x.*
from yyy x
inner join
(
select abc, ad, min(date) as Minad
from dbo.xx
where xx in ('xx' ,'xx' ,'xx' ) AND Activity_Date >= 'mm/dd/yyyy'
group by case_ID, Activity
)
as b on a.abc = b.abc and a.ad=b.Minad
where (a.ad)>= 'mm/dd/yyyy' and a.abc in ('xx' ,'xx' ,'xx' ) AND B.xx  IS NOT NULL
group by a.abc,a.xx,b.xx

 

PROC Star
Posts: 251

Re: sql query conversion to proc sql

I do not completly understand the query. But most of it should work in Proc SQL too. Except few changes where you mention date columns and at is not null.

 

Activity_Date >= 'mm/dd/yyyy'

to

Activity_Date >= 'ddmmmyyyy'd  example '21jul2017'd

 

is not null

to

is not missing

 

 

 

Occasional Contributor
Posts: 5

converting sql query into sas proc sql

need help to convert  below sql query into proc sql or data set query of sas 


Select x.*
from yyy x
inner join
(
select abc, ad, min(date) as Minad
from dbo.xx
where xx in ('xx' ,'xx' ,'xx' ) AND Activity_Date >= 'mm/dd/yyyy'
group by case_ID, Activity
)
as b on a.abc = b.abc and a.ad=b.Minad
where (a.ad)>= 'mm/dd/yyyy' and a.abc in ('xx' ,'xx' ,'xx' ) AND B.xx  IS NOT NULL
group by a.abc,a.xx,b.xx

PROC Star
Posts: 547

Re: converting sql query into sas proc sql

As I read it, there is nothing in your code not supported by PROC SQL in SAS, so simply wrap it inside a PROC SQL call.

Occasional Contributor
Posts: 5

Re: converting sql query into sas proc sql

But it's not working so syntax error
It gives syntax error
PROC Star
Posts: 547

Re: converting sql query into sas proc sql

What error appears in the log?

Super User
Posts: 17,730

Re: converting sql query into sas proc sql

 

Post your FULL LOG.

PROC Star
Posts: 7,356

Re: converting sql query into sas proc sql

If activity_date and ad are SAS dates, and 'mm/dd/yy' are actually numbers, you should use the form:

AND Activity_Date >= 'ddmonyyyy'd

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 547

Re: converting sql query into sas proc sql

@art297 good catch Smiley Happy

Ask a Question
Discussion stats
  • 8 replies
  • 254 views
  • 0 likes
  • 5 in conversation