DATA Step, Macro, Functions and more

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

Reply
Occasional Learner
Posts: 1

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

[ Edited ]

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;

Occasional Contributor
Posts: 12

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

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

Frequent Contributor
Posts: 103

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

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

 

________________________

- That still only counts as one -

Occasional Contributor
Posts: 12

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

[ Edited ]

 

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

 

 


  

 

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 114 views
  • 0 likes
  • 3 in conversation