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;
i think you are missed to assign the character format to the variable Store_No
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
- Cheers -
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.