About LinusH

LinusH

Some kind of sort operation is needed, regardless of technique chosen. I would go for the approach of having the data sorted by id (assume facilities within each id is what you are looking for), facility and start date. Then use REATAIN for two new variables to keep your start and and dates across observations. And then an explicit OUTPUT when the gap is greater than 31 days or your reach an new id/facility combination.

LinusH

SInce you use the term "Project", does it mean you are in Enterprise Guide? Regardless, I would create a copy of the project, and then delete nodes in each (maybe keep another copy of the original until verified ok).

LinusH

"How can I know what is the libaname code that connect between sas and tera here??? I dont need to run the libaname in order to run it but I think that in background it is running but I dont know what libname is running ." Do you want to know specifically what Terdata instructions the libname statement triggers? Try this before your libname: options sastrace="d,,,ds" sastraceloc=saslog nostsuffix msglevel=i; If you are interested what SQL is sent down, you can settle for: options sastrace=",,,d" sastraceloc=saslog nostsuffix msglevel=i;

LinusH

Unfortunately SAS SQL does not support window functions. And I can't see how to solve this in ANSI 1992 SQL, at least easily. The data step is probably your best option here.

LinusH

What kind of assets are trying to reach, and how do you intend to use them (from SAS)?

LinusH

Is your aim to create a smaller permanent data set? Why limit yourself to SQL? If you have SAS/STAT licensed PROC SURVEYSELECT is your go-to for creating samples. If our data set is sorted, you could use a data step to read the first n observations of the specified BY groups.

LinusH

The general recommendation when doing updates/appends to a table with indexes, drop the index and recreate after the update/insert operation. I think the reason is that SAS needs to update the index record by record during an update, whereas a create index operation can do a single sort through operation. I think this applies to your scenario as well.

LinusH

I can't find any specific recommendations for ACT:s, more is written about ACE:s. If it's possible you could try to transform your ACT:s and implement ACE:s instead, before migrating? Here' are some starting points: https://documentation.sas.com/doc/en/sasadmincdc/v_067/calcontentmig9x/p0lui94y7l3fz5n1uzg6rgzgdyjy.htm (Section Authorization) https://documentation.sas.com/doc/en/sasadmincdc/v_067/calauthz/p0g0vol9j5o4t0n1381yo6bc6slp.htm

LinusH

There are many ways to input data, one way to fix this is by using formatted input: input study $1000.;

LinusH

There are a lot of parameters that can affect performance in such use case. If few questions: - Where does your comparison SQL Server reside (vs Databricks)? - Have you tried to load this data by other means into Databricks? Locally (to rule out actual load problems), remotely (compare with SAS). Some hints might be given if you active tracing: options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

LinusH

Sounds like you missed the Plans + Pricing tab?: At least some of your questions could be answered by analysing this tab.

LinusH

You don't need SAS/AF to run AF applications, but you need it to edit them. I don't think you can rely on that running/editing an AF application will be visible in the users SASUSER library. If there's a cost, there should be business responsible for the applications, and you should together be able to verify the requirements of the system.

LinusH

What error are you getting? If you unsure what it means issue options msglevel=i sastrace='d,,d' sastraceloc=saslog nostsuffix; before the libname statement. Assuming the fire wall is open. Have you tried the connection from a non-SAS client?

LinusH

PROC COMPARE compare SAS data sets, not flat files. To be able to compare those files, you need to import them back again into SAS. You could try to add a PROC COMPARE step for the data sets that is the basis for the files, depending on how they are created of course. Maybe you could look at UNIX commands as diff and comm instead?

LinusH

My experience of sending this type of errors to SAS support isn't great, they can rarely point out what causes the error. So I have usually solved it by restructuring the query. How large are the tables? (in saswork by looking into you program)