Need Help in Converting Access Database sql to SAS SQL

[Eshan]

Hi there I am trying to move a process that runs in Access database and creates a couple of reports and now i am trying to change that process into SAS.

I've read all the tables  from Access Database that these reports use into SAS and also extracted the underlying queries from Access into SAS and making changes to the code to SAS compatible.

I've around 6 to 7 SQL queries and in those, i've come across the following statments and I am not sure about how to convert into SAS so really need some help out here.

The reports are monthly and weekly and they are trying to create some scores based on two variables for each id within the time period.

1. Format(Sum(Val(IIf([dataset1].[Var1]=0,0,IIf([dataset1].[var2]=0,1,IIf([dataset1].[var1] Is Null Or [dataset1].[var2] Is Null,0,IIf(Abs([dataset1].[var1]/[dataset1].[var2])>=1,1,

    IIf(Abs([dataset1].[var1]/[dataset1].[var2])<1,Abs([dataset1].[var1]/[dataset1].[var2])))))))),"Fixed") AS SCORE

2. Sum(Val(IIf([dataset1].[var1]=0,0,IIf([dataset1].[var2]=0,1,IIf([dataset1].[var1] Is Null Or [dataset1].[var2] Is Null,0,

    IIf(Abs([dataset1].[var1]/[dataset1].[var2])>=1,1,IIf(Abs([dataset1].[var1]/[dataset1].[var2])<1,0))))))) AS COUNT

3. Format(Sum(CDbl([dataset2].score)),"Fixed") AS SCORE

Thanks

Eshan

[LinusH]

Since most of us is not MS Access specialists, you could get more help if you describe your query in pseudo code, and/or show some sample input data and desired output.

[Tom]

What do you think those statements do?  Have you looked on the MS web site for the definition of the functions referenced.

First remove the [] as they are not needed (or wanted) in SAS.

I have no idea what FORMAT does.  If you want to convert a SUM() to something other than a number use PUT().  But perhaps it does something like ROUND() instead?

I have no idea what VAL() does.

Looks to me like CDBL will convert the type of the variable.  You can remove that as SAS only has one numeric type.

ABS() should work the same.

IIF() should be replaced with CASE to make SQL compatible.  But you might find that the SAS function IFN() does something similar to IIF().

[overmar]

Format(Sum(Val(IIf([dataset1].[Var1]=0,0,IIf([dataset1].[var2]=0,1,IIf([dataset1].[var1] Is Null Or [dataset1].[var2] Is Null,0,IIf(Abs([dataset1].[var1]/[dataset1].[var2])>=1,1,

    IIf(Abs([dataset1].[var1]/[dataset1].[var2])<1,Abs([dataset1].[var1]/[dataset1].[var2])))))))),"Fixed") AS SCORE

For reference it doesn't look like there is a false statement in the iif statements so I am expecting that false in this case would report as missing, but I could be wrong.

Converts to

proc sql;

create table ??? as

select ID, (sum(Case when dataset1.var1 = 0 then 0 when dataset1.var2 = 0 then 1 else . end), (case when dataset1.var1=. then 0 when dataset1.var2 = . then 0 else . end), (case when (abs(dataset1.var1/dataset1.var2)) >=1 then 1  else (abs(dataset1.var1/dataset1.var2)) end)) format=(whatever the "Fixed" format is) as score

from dataset1

group by ID;

quit;

The question though that I would need answered before figuring anything else out would be should var1 and var2 be evaluated separately, ie should you be asking if var1 = 1 then 1 else if var1 = 0 then 0 else if var1 = . then 0 or do you want to include both in each of the statements?

The easiest way to rewrite this is to say what each of the conditions are (as I understand them)

if dataset1.var1 = 0 then 0

if dataset1.var2 = 0 then 1

if dataset1.var1 = . then 0

if dataset1.var2 = . then 0

if abs(dataset1.var1/dataset1.var2) >= 1 then 1

if abs(dataset1.var1/dataset1.var2) < 1 then abs(dataset1.var1/dataset1.var2)

write a case statement for each of these (similar to what I wrote earlier including the else (value) end statements), put a sum statement outside of all of the case statements, and group by the unique ID. This should give you the same report values as long as you specify the variables in the same way.