BookmarkSubscribeRSS Feed
EBrunetti
Calcite | Level 5

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;

3 REPLIES 3
Madansas7b
Obsidian | Level 7

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

Oligolas
Barite | Level 11

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 -

Madansas7b
Obsidian | Level 7

 

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

 

 


  

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 705 views
  • 0 likes
  • 3 in conversation