You can accomplish this with SQL Passthrough using the ROW_NUMBER() function in SQL. Whether it would be faster than a DATASTEP is unknown to me though. ... View more

Do you have sample data?    is your example above VAR1 = 200, VAR2 = Example, VAR3 = Text?   Edit: It sounds like you just want to use CAT function not CATX? ... View more

Not even a temp table but is there something preventing you from just pulling the list into a dataset? Lets assume the list is on your server, dbo.LIST.   Can you not just do: proc sql; connect to odbc (DSN = Warehouse_Name); create table list as select * from connection to odbc ( select id from dbo.LIST ); disconnect from odbc; quit; Then repeat your prior code and INSTEAD of doing the variable just do IN (SELECT ID FROM LIST)?   If there's duplicates then just do a simple proc sort nodup on the LIST dataset at that point. ... View more

Your literally exceeding the max length by 6 values.    It may have been mentioned already but why not just select the list into a separate table/dataset and then have you IN statement reference that table/dataset?    Edit: I'm confused with your original post. I select Millions of records into datasets every day and cross reference these with PROC SQL. Are you actually selecting your list into a dataset? ... View more

Other than changing your Max to 34 (as anything above 34 is irrelevant... 8+8 = 16, 50-16 = 34) this looks like a correct way of doing it. ... View more

This isn't right but it'll get you close. Maybe spark some ideas of your own.   %let NObs = 10; data Test(keep=u n1 n2 n3); call streaminit(123); a = -1; b = 1; Min = 8; Max = 34; do i = 1 to &NObs; u = rand("Uniform"); /* decimal values in (0,1) */ n1 = min + floor((1+Max-Min)*u); /* integer values in Min..Max */ n2 = min + floor((1+Max-Min)*u); /* integer values in Min..Max */ n3 = 50 - n1 - n2; output; end; run; ... View more

For sorting (you may have to play with it depending on your data) although it's not 100% necessary, it is extremely useful so you can ensure your LAG function is working correctly.   PROC SORT DATA = Have OUT = Want; BY AccountNumber Period; RUN; Once your data is sorted how you want it, I would recommend inputting ROW_NUMBER() function with PROC SQL. I'm assuming you want the LAG to be the prior period for the given Account Number?   If so you would do something like this: PROC SQL; CREATE TABLE Want AS SELECT * ,ROW_NUMBER() OVER(PARTITION BY Period ORDER BY AccountNumber) FROM Have; QUIT;  Once your ROW_NUMBER() function is lining up with the data the way you want it you can then implement LAG() using the same OVER(PARTITION BY) as your ROW_NUMBER.    Sometimes in these cases your data will be reversed so the PRIOR period you want will actually be row 3 while the current period is row 2. If this is the case simply switch your ORDER  BY in the ROW_NUMBER (or LAG) to DESC/ASC and that should fix it. ... View more