The variable named LENGHT is probably a mis-spelling of LENGTH, and this improperly spelled variable appears in your code. This mis-spelled variable has never been assigned a value so you see it is always missing.   NORMAL(0)*2 is a random number generator, multiplied by 2.   LENGTH always has a value of 100 because nowhere in the code does the value get changed, probably because of the mis-spelling that I mentioned. ... View more

If you literally just want the number of days between 0001-01-01 and 2023-01-01 then you know it is the number of days in 2022 years.   The only question is how to define all those years.   If the years are ALL defined using the Gregorian Calendar convention, then you have 5 groups of 400 years (from 0001-01-01 through 2000-12-31). ndays_0001_through_2000 = 5*intck('day','01jan1601'd,'01jan2001'd) I chose 400 years because that is the longest cycle defined in the Gregorian Calendar, so any 400 year range would do.  I also could have avoided the multiplication by choosing some valid (for SAS date calculations) 2000 year interval, like ndays_0001_through_2000 = intck('day',01jan1601'd,01jan3601'd) And the directly calculated number of days from 2000-12-31 to through 2023-01-01 ndays_2001_through_2022 = intck('day','01jan2000'd,'01jan2023'd); Then just add  ndays_0001_through_2000  +  ndays_2001_through_2022.     ... View more

In this case, CASE WHEN is not necessary, I don't see a use for it here; and in fact its usually better to explain the input data (which you did) and show the desired output data, without specifying any particular coding method.   I didn't produce a column for balance, as it seems this is an imaginary variable that doesn't exist.   proc sql; create table want as select a.account_id ,b.tr_type ,sum(b.tr_amount) as total from account as a left join transaction as b on a.account_id=b.account_id group by a.account_id,b.tr_type; quit; proc report data=want; columns account_id tr_type,total; define account_id/group; define tr_type/across; define total/sum; run;   ... View more

@Rahul_SAS You are confusing 3 different SAS terms Length - This is the amount of "Ram/Disk used to store a variable" Format - This is the way to display a variable  Informat - This is the way SAS reads a variable In your code (below) you are telling SAS to create a variable salary, it will have a default length of 8 bytes, by reading just 8 numeric characters So your 1st record which has 400000455225 as the second variable, SAS only reads the first 8 characters 400000455 data test; infile datalines dlm=','; input name $ salary 8.; datalines; venkat pati raju,400000455225 vivek rattakonda murali,425 kiran kumar segu,9007199254740992 sagar adirala bahu,72057594037927936 ; run; ... View more

LASR is in-memory engine used by SAS Visual analytics on 9.4 platform. https://documentation.sas.com/?docsetId=inmsref&docsetTarget=n0a090rw1gy20un1xvhhbe7deb1i.htm&docsetVersion=2.82&locale=en   CAS is the engine for Viya. It is used to process all analytic workloads on Viya platform. https://documentation.sas.com/?docsetId=pgmdiff&docsetTarget=n1t409khqsu0n8n103122kk0bfzn.htm&docsetVersion=3.1&locale=en#p1rky8y8cgdegzn1qoysr9fgy68y   MAS is used with solutions that publish python or DS2 code. It is available only with selected SAS solutions. https://documentation.sas.com/?docsetId=masag&docsetTarget=p1dhh0p6zuvf9cn1bxhcstz51ddq.htm&docsetVersion=5.1&locale=en   ... View more

@Rahul_SAS wrote: Hi Jag,   Is it an efficient way to use the full merge and load all the data again and again on a daily basis.   Thanks In no way. What you want is not a merge, but a "stack", which is best accomplished by the code @yabwon gave you (it reads and writes in one sequential pass).   If you are actually doing this in a library defined for a remote DBMS, then the DBMS will perform better if you use SQL code (DELETE FROM and INSERT INTO) in an explicit passthrough. But we don't know that, because we only got Excel spreadsheets to look at. ... View more

So HAVE1 is what is loaded as of April 25. You want to drop the last 10 days of data from HAVE1 and insert the last 11 days (10 prior days, plus April 26 data) from the incremental set? ... View more

Your data makes NO sense. Your "have.xlsx" contains only dates in June 2019, while your "want.xlsx" goes back to February 2018 and ends in July 2019. Please make up your mind, and supply USABLE example data that REALLY illustrates what you want, and give us the REAL logic behind it.   See Maxim 42.   Right now, my answer can only be "42". ... View more

appologies. dropped that old spreadsheet and uploaded it with correction. ... View more

I would use a combination of data step and summary procedure such as: data start; set safety.stag3; comp = input(Case_ID_for_QCCOMP,BESt12.); comperr = input(Case_ID_for_QCCOMPERR,BESt12.); run; proc summary data=start nway; class project employee supervisor team qcdate hub; var comp comperr; output out=startsum sum=; run; data want; set startsum; sumcomp = sum(comp,comperr); casepercentage = comp/sumcomp; run; I will say I find wanting to add with variables with something like Case_ID in the name looks a tad odd though. ... View more