Creating an AI Assistant for SAS Viya in 5 steps (@sassoftware/viya-assistantjs) - Part I
Recent Library Articles
Recently in the SAS Community Library: SAS' @kumardeva debunks the myth that developing AI assistants is too hard. He shows you how to use the @sassoftware/viya-assistantjs library to jump start your development.
I have two datasets, both including ID and day. I would like to merge them by ID and day but include the day variables from both files in the data I want, i.e. include them also when there is no match on the day variable.
data have;
input ID Day;
datalines;
1 1
1 2
1 3
;
Data have2;
Input ID Day;
1 .
1 2
1 3
;
Data want;
ID Day Day
1 1 .
1 2 2
1 3 3
... View more
I have a code similiar to this
the macro variable has Value '%678900','%625890','%827189'
proc sql ;
create table as sec;
select * from table1
where var like any (&Value) ;
quit;
It throws an error ERROR: FUNCTION ANY COULD NOT BE LOCATED. Before to escape using like any I used PRXMATCH('~DENIED|INVALID~i',UPPER(DESC)). How to use with macro in proc sql statement?
... View more
If your datastep code or Proc SQL (or any Proc) is more than a page, perhaps several pages, it’s pretty tedious to select all code before you can submit. I have an SQL that is 4 pages long myself... There is a nice solution to this in SAS Studio on Viya 4 as well as in SAS Enterprise Guide and DMS (Base SAS).
In SAS Studio you right mouse anywhere in your datastep/proc and select Run Region, and that “region” is submitted. It has the shortcut key Shift+F8 by default, and you can change this in Options > Manage Keyboard Shortcuts.
In SAS Enterprise Guide and DMS you have to create a “Keyboard macro”. You can export this Keyboard Macro as a .kmf file and give to others so it's easy to share, and I'll share mine right now. You can get this kmf file below in the ZIP SelectCurrentCode. Unzip and click (Import...] in Program > Manage macros and snippets and after importing you assign it to e.g. F8. So having assigned SelectCurrentCode to F8 you would do F8 and then F3 to select the current datastep/proc and run it. How nice is this!? The title says "a single keystroke" but it's actually two, but still... 😉
The KMF file works for DMS as well. Images below just show EG though.
... View more
Hello, I am trying to BULKLOAD a SAS dataset into Snowflake and I am having trouble with the DATETIME column in my SAS dataset. When trying to load it into Snowflake, I get the error message: ERROR: Error executing COPY command: Numeric value '2016-01-08 14:20:34.000000000' is not recognized File '@~/nucleus/xxxxx.gz', line 6187, character 186 Row 6187, column " CLR_CALL_ACTIV_HIST_2016 "["INTERACTION-ID":21] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client. For reference, here is my SAS code: /* Transform the data */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_0;
/* Load in work table containing the pre-transformed file */
SET WORK.CLR_CALL_ACTIV_HIST_2016_0;
/* Transformations */
'CHANNEL-ID-NUM'N = INPUT(STRIP('CHANNEL-ID'N), BEST32.);
IF MISSING('CHANNEL-OBJ-ID'N) OR UPCASE('CHANNEL-OBJ-ID'N) = "N/A" THEN
'CHANNEL-OBJ-ID-NUM'N = .;
ELSE
'CHANNEL-OBJ-ID-NUM'N = INPUT(STRIP('CHANNEL-OBJ-ID'N), BEST32.);
'GROUP-ID-NUM'N = INPUT(STRIP('GROUP-ID'N), BEST32.);
'INTERACTION-ID-NUM'N = INPUT(STRIP('INTERACTION-ID'N), BEST32.);
'DATE'N = INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 1, 10), YYMMDD10.);
FORMAT 'DATE'N YYMMDD10.;
'TIME'N = INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 12, 8), TIME8.);
FORMAT 'TIME'N TIME8.;
'DATETIME'N = DHMS('DATE'N, HOUR('TIME'N), MINUTE('TIME'N), SECOND('TIME'N));
FORMAT 'DATETIME'N DATETIME20.;
'TRANS-POST-PROC-TIME-NUM'N = INPUT(STRIP('TRANS-POST-PROC-TIME'N), BEST32.);
'TRANS-PROC-TIME-NUM'N = INPUT(STRIP('TRANS-PROC-TIME'N), BEST32.);
'TRANS-REC-NUM-NUM'N = INPUT(STRIP('TRANS-REC-NUM'N), BEST32.);
'TRANS-TOTAL-TIME-NUM'N = INPUT(STRIP('TRANS-TOTAL-TIME'N), BEST32.);
'TRANSACTION-ID-NUM'N = INPUT(STRIP('TRANSACTION-ID'N), BEST32.);
RUN;
/* Drop columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_1;
SET WORK.CLR_CALL_ACTIV_HIST_2016_0;
DROP 'CHANNEL-ID'N
'CHANNEL-OBJ-ID'N
'GROUP-ID'N
'INTERACTION-ID'N
'TRANS-ACCEPT-TIME'N
'DATE'N
'TIME'N
'TRANS-POST-PROC-TIME'N
'TRANS-PROC-TIME'N
'TRANS-REC-NUM'N
'TRANS-TOTAL-TIME'N
'TRANSACTION-ID'N
;
RUN;
/* Rename columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_2;
SET WORK.CLR_CALL_ACTIV_HIST_2016_1;
RENAME 'CHANNEL-ID-NUM'N = 'CHANNEL-ID'n
'CHANNEL-OBJ-ID-NUM'N = 'CHANNEL-OBJ-ID'N
'GROUP-ID-NUM'N = 'GROUP-ID'N
'INTERACTION-ID-NUM'N = 'INTERACTION-ID'N
'DATETIME'N = 'TRANS-ACCEPT-TIME'N
'TRANS-POST-PROC-TIME-NUM'N = 'TRANS-POST-PROC-TIME'N
'TRANS-PROC-TIME-NUM'N = 'TRANS-PROC-TIME'N
'TRANS-REC-NUM-NUM'N = 'TRANS-REC-NUM'N
'TRANS-TOTAL-TIME-NUM'N = 'TRANS-TOTAL-TIME'N
'TRANSACTION-ID-NUM'N = 'TRANSACTION-ID'N
;
RUN;
/* Load final table */
DATA WORK.CLR_CALL_ACTIV_HIST_2016;
SET WORK.CLR_CALL_ACTIV_HIST_2016_2;
RUN; Here is my Snowflake connector code: libname NUC_LIB snow
SERVER = "xxxx"
USER = "xxxx"
PASSWORD = "xxxx"
DATABASE = "xxxx"
SCHEMA = "xxxx"
ROLE = "xxxx"
WAREHOUSE = "xxxx"
;
DATA NUC_LIB.CLR_CALL_ACTIV_HIST_2016
(
BULKLOAD = YES
BL_INTERNAL_STAGE = "user/nucleus"
BL_COMPRESS = YES
BL_DELETE_DATAFILE = YES
DBCOMMIT = 1000000
);
SET WORK.CLR_CALL_ACTIV_HIST_2016;
RUN; Essentially, I need the DATETIME columns from SAS to be DATETIME columns in Snowflake.
... View more
I'm conducting an analysis on SAS 9.4 with health insurance enrollment data and want to understand the enrollment patterns before and after a diagnosis. I have monthly data for several years which I want to select 24 rolling months based around a diagnosis date. I want to copy over the select months into new columns based on the start month range. I have a dummy example below where month1-month7 are the enrollment months 1 through 7 and start_month is the number of the start month and end _month is the number of the end month of the range. I want to copy the data into the "next" variables for a range of 4. Adding "output" into the code doesn't solve the issue. The desired output at the end of this code. Thank you! *table creation for 2 examples; data try1; input month1-month7 start_month end_month; datalines; 1 2 3 4 5 6 7 2 4 1 2 3 4 5 6 7 4 7 ; run; proc print data=try1; run; *This straight copies the data into the "next" variables, not based on start/end range; data try2; set try1; array month month1-month7; /*enrollment months*/ array next next1-next4; /*want to copy over select months here*/ do i=1 to 4; /*interested in range of 4 months*/ next{i}=month{i}; /*this straight copies the data*/ end; run; proc print data=try2; run; *HERE IS WHERE I AM STUCK; *Trying to copy the select months based on the start and end range; *This code below only copies the last select month, which is wrong; data try3; set try1; array month month1-month7; /*enrollment months*/ array next next1-next4; /*want to copy over select months here*/ do k=start_month to end_month; /*start month and end month for each person*/ do i=1 to 4; /*interested in range of 4 months*/ next{i}=month{k}; /*this code is incorrect, it just copies the last month */ end; end; run; proc print data=try3; run; /*This is the desired output*/ data want; input month1-month7 start_month end_month next1-next4; datalines; 1 2 3 4 5 6 7 2 4 2 3 4 . 1 2 3 4 5 6 7 4 7 4 5 6 7 ; run; proc print data=want; run;
... View more