I am using an update process in SAS which is an actual SQL query. It is processing very fast in SQL but in SAS, the update process is take a long time. I need help as to how I can I optimize the update sub query from another table   Proc SQL; update vw_SAS_CMDM_CI_General_Insurance set SumInsured=z.SUM_INSURED , TotalPremium=z.NET_PREMIUM from (select POLICYNO,sum(NET_PREMIUM)NET_PREMIUM,sum(SUM_INSURED)SUM_INSURED from DPR.pr_ytd with(nolock) where system_id not in ('manual','RI','manual_adv','manual_adv_reversal') group by policyno ) Z where z.POLICYNO =vw_SAS_CMDM_CI_General_Insurance.PolicyNo; quit;     ... View more

Hello I am running a query in SQL. I have to use multiple pass through in the Proc SQL and the code inside the SQL passthrough is the code of SQL. The issue is there are two tables from two different database.   Let me know how can I do it here   LIBNAME web_srv SQLSVR Datasrc=webservices SCHEMA=dbo USER=sasproduction PASSWORD="***   LIBNAME sqlsas SQLSVR Datasrc=WebServices SCHEMA=dbo USER=sasproduction PASSWORD=**** ;   Below is the query in SQL Select MOT.Policyno as PolicyNo, POLS.PolicyStatus as PolicyStatus ,case when MOT.PolicyEndDate > GETDATE() then 'Y' Else 'N' end POLICY_ACTIVE_INACTIVE_FLG ,MOT.SMCode as SMCode --,PR.Channel M200_Channel --,PR.SM_Name M200_SalesManagerName ,MOT.ProductCode ProductCode ,MOT.PolicyStartDate as PolicyStartDate ,MOT.PolicyEndDate as PolicyEndDate ,MOT.AgentCode AgentCode ,MOT.BasCode BasCode ,isnull(PR.Branch_Code,substring(mot.policyno,1,4)) Branch_Code ,MOT.SumInsured SumInsured --,PR.NET_PREMIUM M400_NetPremium ,MOT.netOD+MOT.netTP as TotalPremium ,DATEDIFF(YY,MOT.PolicyStartDate,MOT.PolicyEndDate) policyTenure ,PR.PAYMENT_MODE as PaymentMode ,POLS.PolicyStatus as CANCELLED_FLAG ,null as EndorsementCnt from webservices..tbl_motor MOT with(nolock) inner join sqlsas..CMDM_Product_Master PM WITH (nolock) on MOT.ProductCode =PM.Product_code Now I want to run the above SQL query in pass through but pass through has single statement. I want it from two different databases in SQL..please help     ... View more

I created a user format. I want to import the format in SAS VA . Is it possible? If yes,What are the ways to do it?   Below is the user based format created in SAS   proc format library= work;   picture indianc   low-high='99,99,99.99'; run;   ... View more

I have created a user format for Indian currency comma in base sas through image.    proc format library= work;   picture indianc   low-high='99,99,99.99'; run;   I was able to create a catalog but I need to import this my SAS VA format option. How can I do it in SAS VA? Please help us with your suggestions. ... View more

  No    Type                   ID        Month                  Amount 2411 Marine Cargo    1001    01APR2015         100 2411 Marine Cargo    1001    01MAY2015         200 2411 Marine Cargo    1001    01JUN2015         300 2411 Marine Cargo    1001    01AUG2015        100 2411 Marine Cargo    1001    01OCT2015         2411 Marine Cargo    1001    01NOV2015         150 2411 Marine Cargo    1001    01DEC2015         50 2411 Marine Cargo    1001    01JAN2016         100 2411 Marine Cargo    1001    01FEB2016          200 2411 Marine Cargo    1001    01MAR2016 2411 Marine Cargo    1001    01APR2016         300 2411 Marine Cargo    1001    01MAY2016        500 2411 Marine Cargo    1001    01JUN2016        20 2411 Marine Cargo    1001    01SEP2016 2411 Marine Cargo    1001    01OCT2016       300 2411 Marine Cargo    1001    01DEC2016 I need the last year month value against the current one. Few sequence in the months column is missing like 01JUL2015, 01SEP2015 in the year 2015 and 01JUL2016, 01JUL2016 and 01NOV2016 , 01JAN2017, 01FEB2017, 01MAR2017 in the year 2016. I need Current year month-last year month ex (01Apr2016-01Apr2015)   I need the below results   No    Type                   ID        Month                  Amount       LY_Amount 2411 Marine Cargo    1001    01APR2015         100 2411 Marine Cargo    1001    01MAY2015 2411 Marine Cargo    1001    01JUN2015         200 2411 Marine Cargo    1001    01JUL2015          2411 Marine Cargo    1001    01AUG2015        100 2411 Marine Cargo    1001    01SEP2015 2411 Marine Cargo    1001    01OCT2015         2411 Marine Cargo    1001    01NOV2015        150 2411 Marine Cargo    1001    01DEC2015         50 2411 Marine Cargo    1001    01JAN2016         100 2411 Marine Cargo    1001    01FEB2016         200 2411 Marine Cargo    1001    01MAR2016 2411 Marine Cargo    1001    01APR2016        300         100 2411 Marine Cargo    1001    01MAY2016        500 2411 Marine Cargo    1001    01JUN2016         20           200 2411 Marine Cargo    1001    01JUL2016                         200 2411 Marine Cargo    1001    01AUG2016                       100 2411 Marine Cargo    1001    01SEP2016 2411 Marine Cargo    1001    01OCT2016 2411 Marine Cargo    1001    01NOV2016                       150 2411 Marine Cargo    1001    01DEC2016                         50 2411 Marine Cargo    1001    01JAN2017                       100 2411 Marine Cargo    1001    01FEB2017                       200 2411 Marine Cargo    1001    01MAR2017   This is just one set of group variables (2411 Marine Cargo   1001) from so many in my entire data.  I need the missing months in the column month for that particular and also the group variable values which is 2411, Marine Cargo, 1001 for the same.   I can then try lag function, lag12 for the same or intnx function or some sort of join.    ... View more