SAS/SQL Script to Qlikview Script

[#1]

Good day,

I have the below SAS code, I want it to code it in Qlikview script,

How should I go about?

Thanks a lot in advance

/* Accounts for which billed amount >0 */ 

data prev_balanced;

set port_&prev_month.(keep=id_acc PREV_STATEMNT_ACCT_BAL ect_flag class);

where PREV_STATEMNT_ACCT_BAL>0;

run;

/* Dataset for transactor accounts */

proc sql;

create table transactor as

select * , 1 as transactor_flag

from  active_accounts_final

where ect_flag ne 1  and  id_acc in

(select id_acc from prev_balanced);

quit;

/* Dataset for revolver accounts */

proc sql;

create table revolver as

select *

from  active_accounts_final

where ect_flag = 1  and  id_acc in

(select id_acc from prev_balanced);

quit

[Gysbert_Wassenaar]

You can use the sql select statements, but not the sas script. And I don't know sas script so I can't translate that part

This will create the transactor and revolver tables:

LEFT KEEP (prev_balanced)

transactor:

select * , 1 as transactor_flag

from  active_accounts_final

where ect_flag ne 1 ;

LEFT KEEP (prev_balanced)

revolver:

select *

from  active_accounts_final

where ect_flag = 1;

Note, you first need to create a connection to the source database before you can load data from it.

[#3]

Thanks Gysbert,

Does that mean I have to create that table prev_balance before executing this piece of code?

[Gysbert_Wassenaar]

Yes, it does.

[#5]

so for the below code:

select  sum(total_limit)

from active_accounts_final

where credit_balance>0

and account_number

not in (select account_number

       from transactor)

I must use the following syntax??:

left keep (transactor)

load sum(total_limit)

from active_accounts_final

where credit_balance > 0

[Gysbert_Wassenaar]

I'm pretty sure that that will not work. In Qlikview Load sum(something) from .... will return only one number if you're not using a group by clause. And the field name will be literally 'sum(something)' if you don't supply a name: sum(something) as Sum_of_Something. And since there's no field name to associate it with other tables your result table will not be associated with anything else in your data model.

Perhaps you should start with explaining what your data sources are and what kind of data model you're trying to create from that.

[#7]

Basically here I'm trying to mimic a SAS code to perform same functionality and to produce the same output (values).

I'm drawing data from a huge file (Portfolio), then I create sub data-sets.

To reference the sub data-sets i have created, then after I perform front end metric calculations.

Those sun-data are prev_balanced, transactor and revolver

Please note: I have already created the sub data-set called active_accounts_final

/* Accounts for which billed amount >0 */ 

data prev_balanced;

set portfolio(keep=id_acc PREV_STATEMNT_ACCT_BAL ect_flag class);

where PREV_STATEMNT_ACCT_BAL>0;

run;

/* Dataset for transactor accounts */

proc sql;

create table transactor as

select * , 1 as transactor_flag

from  active_accounts_final

where ect_flag ne 1  and  id_acc in

(select id_acc from prev_balanced);

quit;

/* Dataset for revolver accounts */

proc sql;

create table revolver as

select *

from  active_accounts_final

where ect_flag = 1  and  id_acc in

(select id_acc from prev_balanced);

  quit

;

[Gysbert_Wassenaar]

I have already created the sub data-set called active_accounts_final

Is it a file, a database table or a table already loaded in the qlikview document? And where does the data for prev_balanced come from?

[#9]

prev_balance data comes from the file portfolio

[Gysbert_Wassenaar]

And the other data? Those come from files too? What kind of files are these files? Text files, excel sheetsl, xml files?