About SASUserRocks

SASUserRocks · ‎07-14-2022

Dear Friends, Need some help. Unable to retrieve results as expected with below query. proc sql; create table dashare.Push_aggregated_table as select c.product, OFFERSTARTDATE as startdate format date11. , OFFERENDDATE as enddate format date11. , CAMPAIGNTYPE, a.Impressions as Imp, b.Clicks as Clicks from ( select INSERTDATETIME,PID,count(distinct PID) as Impressions from IMPRESSIONS_DATA where substr(PID,18,10)='C000001934' group by PID) a left join ( select INSERTDATETIME,PID,count(distinct PID) as Clicks from CLICKS_DATA where substr(PID,18,10)='C000001934' group by PID,INSERTDATETIME ) b on a.PID=b.pid inner join (select min(OFFERSTARTDATE) as OFFERSTARTDATE,max(OFFERENDDATE) as OFFERENDDATE,product,CAMPAIGNTYPE,TREATMENTCODE ,CAMPAIGNCODE from CONTACTHISTORY where CHANNEL = 'Push Notification' group by CAMPAIGNCODE,TREATMENTCODE ) c on substr(a.PID,18,10)=c.CAMPAIGNCODE and substr(a.PID,29,10)= c.TREATMENTCODE group by product,a.PID; run;

SASUserRocks · ‎08-30-2021

Also getting same error while accessing any hive table inside library, each has got different definition

SASUserRocks · ‎08-30-2021

Yes I can access view outside in hive as well as can view data till sas data loader

SASUserRocks · ‎08-30-2021

Automated few sas reports sourcing data from hive schema. Programs were running fine till yesterday, Today encountering below error while accessing hive tables through SAS library and I am clueless. I am able to access hive tables and data through Ambari Hive. 219 H_BASE.ADCB_USER_PAGE_VIEWS A WHERE datepart(input(timestamp,anydtdtm.))> "&lastsdte."d ; HADOOP_1: Prepared: on connection 0 DESCRIBE FORMATTED `db_appldigi`.`ADCB_USER_PAGE_VIEWS` ERROR: Prepare error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. cannot find field adcb_user_page_views from [0:date, 1:segmentname, 2:engagementname, 3:productlabel, 4:channelname, 5:cookie, 6:impressiontimestamp, 7:impressionid, 8:crmid] SQL statement: DESCRIBE FORMATTED `db_appldigi`.`ADCB_USER_PAGE_VIEWS`

SASUserRocks · ‎08-16-2021

proc sql; create table dashare.Total_Click_Impressions_Lead4 as select distinct A.date, A.Product, count( distinct A.ImpressionId) as Impressions, count( distinct B.clickid) as Clicks, count(distinct row_id||x_adcb_prod_group||adcb_utm_camp||utm_medium) as Leads, startdate as start_date ,enddate as end_date from impressions A left join clicks B on A.date = B.date and A.Product = B.ProductLabel left join ( select row_id,x_adcb_prod_group,adcb_utm_camp,utm_medium,x_adcb_last_lead_action_date from DAshare.HYPERPERSONALIZATION_LEADS where x_adcb_last_lead_action_date = '09Aug2021'd ) C on A.date = C.x_adcb_last_lead_action_date and A.Product = C.adcb_utm_camp left join ( select min(date) format date11. as startdate, max(date) format date11. as enddate, product from dashare.Total_Click_Impressions_Lead group by product having max(date) >='09Aug2021'd ) D on A.Product = D.Product group by A.date, A.Product; quit;

SASUserRocks · ‎08-16-2021

proc sql; create table libshare.Total_Click_Impressions_Lead3 as select distinct A.date, A.Product, count( A.ImpressionId) as Impressions, count( B.clickid) as Clicks, count(distinct row_id||x_adcb_prod_group||adcb_utm_camp||utm_medium) as Leads, startdate as start_date ,enddate as end_date from impressions A left join clicks B on A.date = B.date and A.Product = B.ProductLabel left join ( select row_id,x_adcb_prod_group,adcb_utm_camp,utm_medium,x_adcb_last_lead_action_date from libshare.LEADS where x_adcb_last_lead_action_date = '09Aug2021'd ) C on A.date = C.x_adcb_last_lead_action_date and A.Product = C.adcb_utm_camp left join ( select min(date) format date11. as startdate, max(date) format date11. as enddate, product from libshare.Total_Click_Impressions_Lead group by product having max(date) >='09Aug2021'd ) D on A.Product = D.Product group by A.date, A.Product; quit;

SASUserRocks · ‎08-16-2021

Fine, I just took care of this and added extra filter for D block, query still running... proc sql; create table libshare.Total_Click_Impressions_Lead3 as select distinct A.date, A.Product, count( A.ImpressionId) as Impressions, count( B.clickid) as Clicks, count(distinct row_id||x_adcb_prod_group||adcb_utm_camp||utm_medium) as Leads, startdate as start_date ,enddate as end_date from impressions A left join clicks B on A.date = B.date and A.Product = B.ProductLabel left join ( select row_id,x_adcb_prod_group,adcb_utm_camp,utm_medium,x_adcb_last_lead_action_date from libshare.LEADS where x_adcb_last_lead_action_date = '09Aug2021'd ) C on A.date = C.x_adcb_last_lead_action_date and A.Product = C.adcb_utm_camp left join ( select min(date) format date11. as startdate, max(date) format date11. as enddate, product from libshare.Total_Click_Impressions_Lead group by product having max(date) >='09Aug2021'd ) D on A.Product = D.Product group by A.date, A.Product; quit;

SASUserRocks · ‎08-16-2021

A.date and C.lead action date are in different formats, is this okay to equating like this.

SASUserRocks · ‎08-16-2021

changed leads block to below and evaluating the execution. left join ( select row_id,prod_group,x_adcb_prod_code,utm_medium from libshare.leads where lead_action_date = '09Aug2021'd ) C

SASUserRocks · ‎08-16-2021

Thanks a lot for your response. Here Impressions contains 60781 records and clicks contain 2058. and leads contain around 10. Its taking very long time that i force stop every time, commenting some codes , i think the issue started to come for certain products where impressions came very high this month causing this slowness. Do you require sample data here. also i think joining with leads causes more slowness as join with impressions and clicks was giving results within 30 mins..

SASUserRocks · ‎08-16-2021

slight change in query. :- proc sql; create table libshare.Total_Click_Impressions_Lead1 as select distinct A.date,A.Product, count( distinct A.ImpressionId) as Impressions, count( distinct B.clickid) as Clicks , count( distinct row_id||x_adcb_prod_group||x_adcb_prod_code||utm_medium) as Leads , startdate as start_date ,enddate as end_date from impressions A left join clicks B on A.date = B.date and A.Product = B.ProductLabel left join (select * from libshare.leads where x_adcb_last_lead_action_date='09Aug2021'd) C on intnx('day',A.date,0,'B') = intnx('day',C.x_adcb_last_lead_action_date,0,'B') and A.Product = C.adcb_utm_camp left join(select min(date) format date11. as startdate , max(date) format date11. as enddate , product from libSHARE.Total_Click_Impressions_Lead group by product) D on A.Product = D.Product gr

SASUserRocks · ‎08-16-2021

Hello friends, I am facing issue with optimizing this long running proc sql query. I compute daily impressions and clicks and aggregate records along with leads information. This query takes very long time. Please suggest some way forward. Impressions and clicks table also contain only 1 day data. proc sql; create table libshare.Total_Click_Impressions_Lead as select distinct A.date,A.Product, count( A.ImpressionId) as Impressions, count( B.clickid) as Clicks , count( distinct row_id||x_adcb_prod_group||x_adcb_prod_code||utm_medium) as Leads , startdate as start_date ,enddate as end_date from impressions A left join clicks B on A.date = B.date and A.Product = B.ProductLabel left join (select * from libshare.leads where x_adcb_last_lead_action_date='09Aug2021'd) C on intnx('day',A.date,0,'B') = intnx('day',C.x_adcb_last_lead_action_date,0,'B') and A.Product = C.adcb_utm_camp left join(select min(date) format date11. as startdate , max(date) format date11. as enddate , product from libshare.Total_Click_Impressions_Lead group by product) D on A.Product = D.Product group by A.date,A.Product ;

SASUserRocks · ‎06-21-2020

Hi Folks, I am trying hard to change the color of hierarchy label i created for my SAS VA barchart. I checked all style and data color options in right hand side of pane and couldnt do it. As shown in below image, the Carloan,Personal loan hierarchy category coming in blue which is not getting changed even i change value field color. Please help.

SASUserRocks · ‎06-18-2020

Perfect Thanks @s_lassen

SASUserRocks · ‎06-18-2020

Hi , I have a dataset like below that am maintaining in excel . i need to extract week_str from this data set and and insert into another dataset which has date value which can be something between Week_Starts and Week_Ends from the above table.Can someone explaing me the logic in SAS to do the same . I am new to SAS Programming.

Online Status	Offline
Date Last Visited	‎03-22-2024 05:07 AM

Re: mail scheduled in SAS Management console not giving desired output

Re: mail scheduled in SAS Management console not giving desired output

mail scheduled in SAS Management console not giving desired output

File in use error in SAS Email macro

File in use error in SAS email . HTM:L body and proc report grid not c...

Re: subtract numeric value from month parameter in SAS VA

Re: subtract numeric value from month parameter in SAS VA

subtract numeric value from month parameter in SAS VA

subtract numeric value from month parameter

Dynamic subtotal

Sas query optimization

Re: Unable to access hive tables from SAS

Re: Unable to access hive tables from SAS

Unable to access hive tables from SAS

Long running proc sql query.

Re: Proc sql query optimization - Running long hours

Re: Proc sql query optimization - Running long hours

Re: Proc sql query optimization - Running long hours

Re: Proc sql query optimization - Running long hours

Re: Proc sql query optimization - Running long hours

Re: Proc sql query optimization - Running long hours

Proc sql query optimization - Running long hours

Change color for Barchart hierarchy labels

Re: Insert week string from another dataset matching start date and en...

Insert week string from another dataset matching start date and end da...