Your data is already sorted by ID, so this will do the trick: data want; merge dat1 dat2 dat3; by id; run; ... View more

The elements of a fully-qualified table reference in SAS don't correlate to physical folders in the file system. So, if you have write access to the SASUSER library, it's possible to create a subfolder there, you'd need to assign a different libref instead of using three-level notation. If that's what you want, then you could go about it like this (examples were tested using PC SAS): 1. Identify the physical location of the SASUSER library. The PATHNAME function can do that for you: %put NOTE: The SASUSER library is located in %qsysfunc(pathname(sasuser)); In the log: NOTE: The SASUSER library is located in C:\Users\myUserID\Documents\My SAS Files\9.4 &nbsp You can use the DLCREATEDIR option to make SAS create a folder in the file system when assigning a libref, if the folder does not exist:   options dlcreatedir; LIBNAME SUB "%qsysfunc(pathname(sasuser))\mySubFolder"; options nodlcreatedir; From the log: NOTE: Library SUB was created. NOTE: Libref SUB was successfully assigned as follows: Engine: V9 Physical Name: C:\Users\myUserID\Documents\My SAS Files\9.4\mySubFolder Now, data sets written to the SUB library will be stored in the new mySubFolder folder. data sub.have; set work.have; run;   ... View more

Se apenas precisas listar rows que contenham um valor de data/hora dentro de 96 horas de uma data especificada, pode usar PROC SQL e a cláusula BETWEEN. Veja: /* Aqui só estou criando ums dados falsos */ data meus_dados; call streaminit(12345); length Instalacao DataDoNoticia 8 Nota $10; DataDoNoticia='15APR2024:00:00:00'dt; format DataDoNoticia E8601DT18.; array palavra [5] $10 _temporary_ ('Ótimo','Bom','Mal','Locura','Péssimo'); DO Instalacao =1 to 10; do I= 1 to rand('integer',2,5); DataDoNoticia+(86400*I); Nota=palavra[rand('integer',1,5)]; output; end; end; drop i; run; title 'Exemplo dos Meus Dados'; proc print data=meus_dados(obs=5); run; Exemplo dos Meus Dados Obs Instalacao DataDoNoticia Nota 1 1 2024-04-16T0:00:00 Péssimo 2 1 2024-04-18T0:00:00 Mal 3 1 2024-04-21T0:00:00 Péssimo 4 1 2024-04-25T0:00:00 Péssimo 5 2 2024-04-26T0:00:00 Locura     /* Aqui começa o trabalho */ %let comeco="25APR2024:00:00:00"dt; %let fim=&comeco+345600; %put %superq(comeco) %superq(fim); title "Instalações com noticias dentro de 96 horas desde %qscan(%superq(comeco),1,%str(%"))"; proc sql; select Instalacao ,min(DataDoNoticia) format=E8601DT18. as PrimeiraData ,count(*) as Conta from meus_dados where DataDoNoticia between &comeco and &fim group by Instalacao ; quit; Instalações com noticias dentro de 96 horas desde 25APR2024:00:00:00 Instalacao PrimeiraData Conta 1 2024-04-25T0:00:00 1 2 2024-04-26T0:00:00 2   ... View more

While SQL is sometimes simpler to write, the SQL processor does a lot of "behind the scenes" work (sorting, etc.) on your behalf. An individual SQL step will often be more resource-intensive than a PROC SORT / DATA step combo.   ... View more

First, let's make some data robust enough for testing.    data have; input Hour HR Temp Resp SepsisLabel Patient_ID; datalines; 0 . . . 0 3205 1 76 . 20 0 3205 2 78 . 20 1 3205 3 81 . 16 1 3205 0 . . . 0 3206 1 76 . 20 0 3206 2 78 . 20 0 3206 3 81 . 16 0 3206 0 . . . 1 3207 1 76 . 20 1 3207 2 78 . 20 1 3207 3 81 . 16 1 3207 0 . . . 1 3208 1 76 . 20 1 3208 2 78 . 20 0 3208 3 81 . 16 0 3208 ; In this dataset, Patient_ID 3205 and 3208 had a change in SepsisLabel from 0 to 1 or from 1 to 0. Patient_ID 3206 and 3207 had no change in SepsisLabel. As I understand the problem, you want to keep all rows for Patient_ID 3205 and 3208, and exclude all rows for Patient_ID 3206 and 3207. If so, here's one approach:   proc sql; select * from have as h inner join (select Patient_ID from(select distinct Patient_id, SepsisLabel from have) group by Patient_ID having count(*) >1) as w on h.Patient_ID=w.Patient_ID ; quit; The SQL subquery identifies those Patient_ID values having more than one value for SepsisLabel. Those Patient_ID values are used to select the desired rows from the original table.      ... View more

That changes the LIBNAME statement a bit, but it should still work. And ODBC connection requires setting up an ODBC  data source that contains the connection info. The system administrator would have set it up, and the other SAS users should know it, too. In this example, the data source is "mydatasource".  Then, the LIBNAME statement would look like this:   libname mydblib odbc datasrc=mydatasource user="my_user_1[schema_owner]" password="MyPassword1";   The rest of the code would work the same.    ... View more

@TomC4188 The SAS code above shows that, without a doubt, SAS passes the 'personal-account-name[service-account-name]' combo straight to Oracle as-is, and that Oracle recognizes the proxy connection and grants access. Have your SAS user try this code: /* Replace these values with those that work for your system: */ %let OracleServer=//my.oracle.server:1521/MY_INSTANCE; %let myUserID=my_user_1; %let myPassword=MyPassword1; %let theProxyID=schema_owner; /* DO NOT modify the code below this line */ libname myconn oracle path="&OracleServer" user="&myUserId[&theProxyID]" password="&myPassword"; proc sql; connect using myconn; select * from connection to myconn (select sys_context('USERENV','PROXY_USER') PROXY_USER, sys_context('USERENV','SESSION_USER') SESSION_USER from dual); disconnect from myconn; quit; You should get results back with no error.  If this works for your user, please click the "Accept as Solution" button so others looking for help can find the answer more easily. ... View more

Using the Oracle doc  as a guide, I created these users  -- Create SCHEMA_OWNER. create user schema_owner identified by SecretPassword1; grant create session to schema_owner; -- Create the proxy user MY_USER_1. create user my_user_1 identified by MyPassword1; grant create session to my_user_1; -- Allow MY_USER_1 to make a proxy connection through SCHEMA_OWNER alter user schema_owner grant connect through my_user_1; After that, this SAS code worked fine for me (I find using a LIBNAME statement and then CONNECT USING in PROC SQL is easier to write and understand): libname myconn oracle path="//my.oracle.server:1521/MY_INSTANCE" user="my_user_1[schema_owner]" password="MyPassword1"; proc sql; connect using myconn; select * from connection to myconn (select sys_context('USERENV','PROXY_USER') PROXY_USER, sys_context('USERENV','SESSION_USER') SESSION_USER from dual); disconnect from myconn; quit;                     Results PROXY_USER SESSION_USER MY_USER_1 SCHEMA_OWNER   Hope this helps. May the SAS be with you! Mark ... View more

I suspect that your SAS session encoding does not match the encoding of your Hadoop connection. By default, Hadoop uses UTF-8 encoding. You can check the encoding of your SAS session using PROC OPTIONS: proc options option=encoding; run; If your SAS session is not UTF-8 and you are running PC SAS, go to the start menu, find the SAS group, and look for "SAS 9.4 (Unicode Support)". Try running your code in that configuration. If you are not running a locally installed SAS, you may not have the ability to change the encoding. In that case, consider using a LIBNAME statement to connect to Hadoop with the SUB_CHAR= option, and then using the CONNECT USING statement in your SQL. Example pseudo-code provided here: libname hdp hadoop user='my-user-name' pwd='my-password' server='hive.server.com' sub_char=QUESTIONMARK; proc SQL; connect using hdp; create table work.Temp as SELECT * FROM CONNECTION TO hdp (SQL Passthrough code) ; disconnect from hdp; quit; May the SAS be with you! Mark ... View more

In the code I provided, the path is set in line #9: %let csvpath=/home/myUserId/myFiles; As noted, the code before that line was designed to identify the system path to your WORK library and create a folder there for testing. If you have a specific location in mind, you can skip all of the code before line 9, and then modify that code to point to the location of your choosing. For example, say your SAS is running on Linux, and you want to access the files in the path /home/myUserID/myFiles: %let csvpath=/home/myUserID/myFiles;   ... View more

The LIBNAME statement is used to set up a folder location as a  base SAS library. The base SAS library engine ONLY reads and writes SAS datasets (.sas7bdat). So this statement in your original code cannot possibly be used to read in .CSV files: libname mylib odbc noprompt="myconneciton" schema=DBO Connection=global; Your code does not include information about the file system path for the folder containing the .CSV files. You'll need to know the path, and that path will have to be accessible from the system that is running SAS. If your SAS is on a Windows system, that might look like "C:\temp\mydata", on a Linux system, "/home/myuserID/mydata". Most of the code sample I'm providing here is just setup that identifies the path to your WORK directory, makes a folder there, and creates 2 CSV files for you to play around with. Because the folder is in WORK, it will automatically be deleted when you shut down your SAS session, so no need to worry about cleaning up. The PROC IMPORT step the final section is an example of how to import a CSV file. When you run this code, it will import the fish.csv file. For practice, try modifying that code to import the iris.sv file instead.     /*********************************************************************** Setup code ***********************************************************************/ /* Make a subdirectory to store the CSV files in your WORK library folder so that it will all automatically be deleted when SAS shuts down */ %let workpath=%qsysfunc(pathname(work)); %let rc=%qsysfunc(dcreate(myCSVfiles,%superq(workpath))); %let csvpath=%superq(workpath)/myCSVfiles; %put NOTE: &=csvpath; /* Make a some CSV files to play with */ proc export data=sashelp.fish (obs=5) outfile="%superq(csvpath)/fish.csv" dbms=csv replace; run; proc export data=sashelp.iris (obs=5) outfile="%superq(csvpath)/iris.csv" dbms=csv replace; run; /*********************************************************************** OK - sample code to do the work starts here: ***********************************************************************/ /* Import a CSV file */ proc import datafile="&csvpath/fish.csv" DBMS=CSV out=work.csv_fish replace; getnames=YES; run; /* Check out the results */ proc print data=work.csv_fish; run; You can find more information about PROC IMPORT by clicking this link. May the SAS be with you! Mark ... View more

The %INCLUDE statements in your program are intended to read in text from an external file and process it as SAS code.   %INCLUDE "/home/u63616417/sasuser.v94/v24_y2022/AGESEXV2.SAS"; %INCLUDE "/home/u63616417/sasuser.v94/v24_y2022/SCOREVAR.SAS"; %INCLUDE "/home/u63616417/sasuser.v94/v24_y2022/V24H86H1.SAS"; %INCLUDE "/home/u63616417/sasuser.v94/v24_y2022/V24H86L1.SAS"; %INCLUDE "/home/u63616417/sasuser.v94/v24_y2022/V24I0ED2.SAS"; Your log indicates that those files don't really exist, so a WARNING and ERROR is generated for each bad file name: WARNING: Physical file does not exist, /home/u63616417/sasuser.v94/v24_y2022/AGESEXV2.sas. ERROR: Cannot %INCLUDE member AGESEXV2 in the aggregate IN0. WARNING: Physical file does not exist, /home/u63616417/sasuser.v94/v24_y2022/V24I0ED2.sas. ERROR: Cannot %INCLUDE member V24I0ED2 in the aggregate IN0. WARNING: Physical file does not exist, /home/u63616417/sasuser.v94/v24_y2022/V24H86L1.sas. ERROR: Cannot %INCLUDE member V24H86L1 in the aggregate IN0. WARNING: Physical file does not exist, /home/u63616417/sasuser.v94/v24_y2022/V24H86H1.sas. ERROR: Cannot %INCLUDE member V24H86H1 in the aggregate IN0. WARNING: Physical file does not exist, /home/u63616417/sasuser.v94/v24_y2022/SCOREVAR.sas. ERROR: Cannot %INCLUDE member SCOREVAR in the aggregate IN0. You specify the file by providing a combination of path and filename like this:  "/home/u63616417/sasuser.v94/v24_y2022/AGESEXV2.SAS" and remember, everything is case-sensitive in Linux.  It is unusual for SAS program files to be located in your SASUSER folder structure. Ensure your path and filename are correct and the casing is current, too.  ... View more

Is the text in the macro variable consistently laid out like this, and you just want to extract varname1 and varname2? Or does the layout vary? [some text] varname1 <@n> varname2 You provided a sample input value of "[aeterm9] aedecod </@2> aeterm". What would the values of test1 and test2 be if your code was working the way you wanted it to work? Please provide a simple sample of the dataset adae. Your use of the functions VFORMATX and VLABLEX doesn't make without this for context.        ... View more

When I ran the code you posted, work.df5 was not empty: conv key source BetaEstimate RawEffect Mean Square StdzEffect 49 10 AD 0.00000000 0 0.000000 0 69 5 C -0.12500000 -0.25 0.250000 0.5 60 13 CD -0.12500000 -0.25 0.250000 0.5 52 14 ACD -0.12500000 -0.25 0.250000 0.5 79 16 ABCD -0.12500000 -0.25 0.250000 0.5 82 12 ABD 0.25000000 0.5 1.000000 1 62 6 AC 0.37500000 0.75 2.250000 1.5 81 8 ABC -0.37500000 -0.75 2.250000 1.5 86 15 BCD -0.37500000 -0.75 2.250000 1.5 81 4 AB 0.50000000 1 4.000000 2 88 7 BC -0.62500000 -1.25 6.250000 2.5 88 11 BD 2.25000000 4.5 81.000000 9 60 9 D -2.75000000 -5.5 121.000000 11 60 2 A -4.00000000 -8 256.000000 16 89 3 B 12.00000000 24 2304.000000 48   but there were several problems with the code. Within the halfnorm macro definition: The PROC GPLOT step  (lined 35-39) closes with a RUN CANCEL, so the step never executes. Why include this code if you don't intend it to run? Macro mysort gets called on line 41, but it was never defined. So, of course, it cannot execute... On lines 119 to 123:   %macro save_dataset(df5); proc datasets library=work nolist; save df5; quit; %mend; %save_dataset(df5);   The definition for macro savedataset will always delete everything from the WORK library except DF5. It's hard-coded - the parameter on the macro definition is totally useless. Finally, at the end:   data dsn2; set work.dsn2(obs=10); run; proc print; run; Because you deleted everything from the WORK library except DF5, there is no work.dsn2 dataset for you to read here. And honestly, if you just wanted to print 10 observations, you shouldn't overwrite the original dataset - just use:   proc print data=work.dsn2(obs=10); run; I think that there are several macros defined here that would have been better written as plain SAS code.  ... View more