so, in an effort to rename certain things in this code i've done the following.. .not sure why the second where statement is failing to rename the ip info, in fact it doesn't show up at all in my final data set? ? proc sql; connect to netezza(server=uxcrdbp3 database=EDW_METRICS_NZ_PROD01 authdomain=NZAuth); create table work.apps as select * from connection to netezza ( select a.div_no ,a.application_id ,a.appl_store_number ,a.card_purch_actv_store_no ,a.entry_Date ,a.true_app_ind ,b.ipaddress ,b.IP_LOC_CITY ,b.DEVICE_ALIAS from nzschema.application_curr_vw A left join EDW_NZ_PROD01.nzschema.IOVLOG_IOVAT_LOG B on a.application_id=b.application_id where a.entry_date between &date1 and &date30 and a.div_no in (&DivNo) and a.true_app_ind=1 and a.appl_store_number not in(&Test_Store)); quit; /* When the application is from a web location "store 9990", this orders the web application to the proper store#*/ proc sql; create table work.apps1 as select * ,case/*Store Num 9481*/ when Appl_Store_Number='9990' and card_purch_actv_store_no>0 then left(put(card_purch_actv_store_no,7.)) when Appl_Store_Number='9990' and IPADDRESS='50.235.193.210' then '9481' when Appl_Store_Number='9990' and IPADDRESS='172.58.11.96' then '9481567' when Appl_Store_Number='9990' and IPADDRESS='50.128.141.125' then '9481IP3' when Appl_Store_Number='9990' and IPADDRESS='50.243.249.213' then '9481004IP' when Appl_Store_Number='9990' and IPADDRESS='70.126.152.26' then '9481005 IP address' when Appl_Store_Number='9990' and IPADDRESS='73.54.9.192' then '9481006 IP address' when Appl_Store_Number='9990' and IPADDRESS='76.108.188.73' then '9481007 IP address' when Appl_Store_Number='9990' and IPADDRESS='99.159.198.76' then '9481008 IP address' /*Store Num 6181*/ when Appl_Store_Number='9990' and IPADDRESS='162.199.196.117' then '6181 IP address/activation' /*Store Num 6451*/ when Appl_Store_Number='9990' and IPADDRESS='74.64.27.127' then '6451 IP address/activation' /*Store Num 8001*/ when Appl_Store_Number='9990' and IPADDRESS='50.186.155.15' then '8001 IP address/activation' /*Store Num 5631*/ when Appl_Store_Number='9990' and IPADDRESS='98.148.135.186' then '5631 IP address/activation' /*Store Num 2131*/ when Appl_Store_Number='9990' and IPADDRESS='98.148.135.186' then '2131 IP address/activation' when Appl_Store_Number='9990' and IPADDRESS='71.240.170.83' then '2131 IP address/activation' /*Store Num 581*/ when Appl_Store_Number='9990' and IPADDRESS='67.77.44.26' then '581 IP address/activation' /*Store Num 6681*/ when Appl_Store_Number='9990' and IPADDRESS='50.248.9.113' then '6681 IP address/activation' when Appl_Store_Number='9990' then 'x'||(IPADDRESS) else Appl_Store_Number end as Store_No from work.apps; quit; /*This orders the raw data by DIV Store Num and transaction date.*/ proc sort; by div_no store_no entry_date; run;
... View more