First When clause works for renaming, second one fails???

EBrunetti — Wed, 22 Mar 2017 16:45:33 GMT

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;

Re: First When clause works for renaming, second one fails???

Madansas7b — Thu, 30 Mar 2017 08:02:22 GMT

i think you are missed to assign the character format to the variable Store_No

Re: First When clause works for renaming, second one fails???

Oligolas — Thu, 30 Mar 2017 12:19:54 GMT

Hi,

as far as I can see, your code is OK. But check your data, you might have leading or trailing blanks in Appl_Store_Number/IPADDRESS

try [...] when strip(Appl_Store_Number)='9990' and strip(IPADDRESS)='50.235.193.210' then '9481'

PROC SQL;
   CREATE TABLE CLASS AS
   SELECT *,
          CASE
              WHEN age eq 12 AND substr(name,1,1) eq 'J' THEN 'J'
              WHEN age eq 12 AND substr(name,1,1) eq 'L' THEN 'L'
              WHEN age eq 12 AND substr(name,1,1) eq 'R' THEN 'R'
              ELSE 'XX '||name||' XX'
          END AS STore_no
   FROM sashelp.class
   ORDER BY age, name
   ;
QUIT;

btw sort directly in SQL

Re: First When clause works for renaming, second one fails???

Madansas7b — Thu, 30 Mar 2017 14:00:18 GMT

When the type and length of a variable are not explicitly set, SAS gives the variable a default type and length as shown in bellow

x='ABC';

x='ABCDE';

here it will consider as per first obs length

topic Re: First When clause works for renaming, second one fails??? in SAS Programming

First When clause works for renaming, second one fails???

Re: First When clause works for renaming, second one fails???

Re: First When clause works for renaming, second one fails???

Re: First When clause works for renaming, second one fails???