I have the following table    Year    policy_no   As_on_mar2016    As_on_Mar2015         As_on_Mar2014 2013   001                                                                                   100 2013   002                                                                                    200 2013   003                                                                                   100  2014   001                                                    200 2014   002                                                    200                            2014   003 2015   001                   100 2015   002                   300 2015   003                   300   I want the final below output like this in one variable called net_amount  from different variables above. I want As_on_Mar2014 in year 2013 against that policy, As_on_Mar2015 in 2014 and As_on_Mar2014 in 2015. Please note the policies are in tens of thousand in original data   Year    policy_no   Net_Amount            2013   001                  100 2013   002                  200 2013   003                   100                                   2014   001                  200 2014   002                  200                                                           2014   003 2015   001                 100 2015   002                  300                                  2015   003                  300    I tried using arrays   data want; set values; arrays year (3) _temporary_ (2013 2014 2015); arrays varf  (3)     As on Mar2014 As on Mar2015  As on Mar2016; do i= 1 to 3; if year =  year[i] then net amount= varf end; drop (i); output; run;   It does not satisfy my conditions of both year and policyno because the policies in the original data I am using is in thousands compared to year which are 5 to 6 in distinct value so how do I use the loop conditions or other methods                                                        ... View more

For some reason I have to use PROC SQL to do cumulative sum for the number variables but I am unable to do it using Proc SQL   Table1   Fiscal_year   Premium_date  type     premiumamount  sum_assured 2016-17       01JAN2017      corp     100                       100 2016-17       01JAN2017      corp     100                       100 2016-17       01FEB2017      corp     200                       200 2016-17       01FEB2017      corp     200                       200   I used PROC SQL; Create table want as Select fiscal_year, premium_date, type, sum(premiumamount) as YTD_Premiumamount, sum(sum_assured) as YTD_sum_assured from table1 as a group by fiscal_year, premium_date, type quit   I would get result like this fiscal_year   Premium_date  type     YTD_premiumamount  YTD_sum_assured 2016-17       01JAN2017      corp     200             200 2016-17       01FEB2017      corp    400              400   But I want like this which is culmulative sum YTD   fiscal_year  Premium_date  type    YTD_premiumamount  YTD_sum_assured 2016-17       01JAN2017      corp     200             200 2016-17       01FEB2017      corp     600             600   In short it should have cumulative sum of measures until the fiscal year ends in 31mar2017. I have tried in data step using first.var but want in Proc SQL ... View more

I need some help. I have table like this   Policyno  Premimumdate   Type         Premiumamount  11111        01Jan2018        Retail                100 2222         01FEB2018       Retail                200 3333         01MAR018        Corporate          300 4444          01APR2018      Corporate          100 5555          01JUN2018       Retail                100 6666          01JUL018          Retail                 200 7777          01AUG2018       Corporate          300 8888          01SEP2018       Corporate           100   I need an output like this in the sum column. I need YTD (year till date) by type. YTD for last year starts from Apr 2017 to Mar 2018. Current YTD starts from Apr 2018 till date or latest end date. So in second row (policy 2222) for retail type  it will be 100+200=300   Policyno  Premimumdate   Type         Premiumamount          Sum 11111        01Jan2018        Retail                100                       100 2222         01FEB2018       Retail                200                        300 3333         03MAR018        Corporate          300                       300 4444          07APR2018      Corporate          100                       100 5555          01JUN2018       Retail                100                        100 6666          25JUL018          Retail                 200                       300 7777          01AUG2018       Corporate          300                       400 8888          09SEP2018       Corporate           100                       500   Can I  sum based on conditions? I tried taking MONYY7. taking month and year and defining the criteria for fiscal year but how can i add the sum of all retail or corporate starting from Apr 2018 premium amount till premiumamount date values.         ... View more

I am aware of the how to delete the users manually from SAS Management Console but can anyone tell me how to delete users from SAS Management Console using script/code in SAS EG.   Any response would be welcome ... View more

I am using macro variable in datalines. I am getting error ERROR: The macro APPLYLOGIC generated CARDS (data lines) for the DATA step, which could cause incorrect results. The DATA step and the macro will stop executing. Please suggest me code where I can invoke macro variable in the datalines. The below code is for adding sample username to metadata in bulk. I can use the set statement and external file but for learning purpose I want to know function to use macro in datalines.   data employee; length emailAddr $30. userid $30.; input keyid$ name$ displayname$ emailAddr$ memkeyid$ userid$; datalines; P002 IBL11152 Atest1 email1@address P002 IBL\IBL11152 P003 IBL11153 Atest2 email2@address P003 IBL\IBL11153 P004 IBL11154 Atest3 email3@address P004 IBL\IBL11154 P005 IBL11155 Atest4 email4@address P005 IBL\IBL11155 P006 IBL11156 Atest5 email5@address P006 IBL\IBL11156 ; run; %macro loopmacro; %global keyidm namen displaynamem emailAddrm memkeyidm useridm lastrow; data _null_; set employee end=eof; call symput('keyidm'||left(_n_),keyid); call symput('namen'||left(_n_),name); call symput('displaynamem'||left(_n_),displayname); call symput('emailAddrm'||left(_n_),emailAddr); call symput('memkeyidm'||left(_n_),memkeyid); call symput('useridm'||left(_n_),userid); if eof then call symput ('lastrow', left(_n_)); run; %put &lastrow; %mend; %loopmacro; %put &lastrow; /* The meaning of canonical, if you are in doubt 🙂 "Conforming to orthodox or well-established rules or patterns, as of procedure." */ options symbolgen mprint mlogic; /*-------------------------------------------------------- * Example based on MDUIMPC and MDUIMPL macros to import * user info to the SAS Metadata Server. *--------------------------------------------------------- */ /*-------------------------------------------------------- * FILL OUT INFO FOR METASERVER and METAUSER!!!!!!!!! *----------------------------------------------------------*/ options metaserver= "*********" /*Your server Hostname */ metaport=8561 metauser="I*********"" metapass="*********"" metaprotocol=bridge metarepository=Foundation; /* %MDUIMPC macro is used to define "canonical" tables and the data step is used to extract data from external sources and append them to the tables. */ %mduimpc(); /* Create person table */ %macro applylogic; %do i= 1 %to &lastrow; data &persontbla ; %definepersoncols; infile datalines delimiter=',' missover; input keyid name description title displayname; datalines; &&keyidm&i.,&&namen&i., ,CCBG,&&displaynamem&i. ; run;   %end; %mend; %applylogic; /*filename testxml 'filename.xml' lrecl=1024;*/ /*%mduimpl(outrequest=testxml);*/ %mduimpl();   ... View more

Hello Everyone,   I need one help. I have written one piece of code where based on certain conditions, SAS shoots an email to users notifying an event. For example, if there is a file missing or count of records zero in a file. The issue is when I run the code in SAS EG, it shoots an email with email sent log, but when I schedule the same code in SAS Management Console it does not send the email.   I do not face any issue running the code manually in SAS EG, however while scheduling it does not send the email. Do I need to activate some additional settings in Management Console to enable in sending email? What can be the  reason for it? Let me know if you need more information   Below is the sample macro which I execute in the main body of the code and also invoke it %MACRO countobs (outtablename,filename ); proc sql noprint; select count(*) into: chk from &outtablename.; quit; %PUT &chk ; %IF &chk LE 0 %THEN %DO ; options emailsys =smtp emailhost=smtp.#######@#######.com emailid="#######@#######.com" emailpw="#######@#######" emailauthprotocol=login; filename myemail EMAIL to="#######@#######.com" cc=("#######@#######.com" "#######@#######.com" "#######@#######.com") subject= "&filename. is blank for &filename_dt. "; DATA _NULL_; FILE myemail; PUT "Hello"; PUT "&filename. is blank for &filename_dt. and SAS session will be aborted"; PUT "Please note this is an automatic email sent from the system due to zero records in the &filename. file today"; RUN; %abort cancel; %end ; %MEND countobs ;   %countobs(&outtablename,&file_name);   ... View more

data sample; set sashelp.class; run; data sample1; set sashelp.class; run; proc sql; select  memname into :deletelist seperated by ' ' from dictionary.tables where libname = "WORK"; quit; %global deletelist; %put &deletelist; %symdel deletelist; %Macro deletetables; %if %symexist (deletelist) %then %do; Proc datasets library= work nolist; delete &deletelist; quit; %end; %else %do; %put The macro variable deletelist does not exsit; %end; %mend deletetables;   %deletetables;   In the above examples I am keeping this piece of program in my job flow. This is just an example to what the logic I need for my project. When macro variable deletelist is created it has two values sample sample1 when you run first time but when it is deleted and the next time you run the same program I get the message with an error. "WARNING: Apparent symbolic reference DELETELIST not resolved" because there is no values in the macro variable now and it is already deleted when the program ran first time, how can I resolve this with other method/logic to avoid error? Does %symexist function check the existence of macro variable? I tried %sysfunc(exist but it doesn't work in case of macro variable like this. ... View more