rss.livelink.posts-in-node

Dataflux: shceduling process job

HeidiDT — Mon, 31 Oct 2022 06:34:13 GMT

Hi All

I need to schedule my Dataflux jobs to run via CA7. I created a small test process job, with a data job embedded. I then created a shell script to call the process job and created a JCL on the mainframe to run this shell script. The process job gets kicked off, but it is unable to open the embedded data job. I am attaching the log. Please could anyone help?

Many thanks!

DataFlux bulkload to SQL Server with datetimeoffset

HeidiDT — Mon, 31 Oct 2022 06:07:06 GMT

Hi All

I am ding a load of 183m rows from Dataflux into a SQL Server table. The current job is just a normal insert, but I am playing around with the bulk row count option to see if I can't speed it up (it ran for 42 hours). However, I am getting an error with a datetimeoffset field. The field seems to be a character value in Dataflux, so I would have thought it would be coerced into a datetime value, but I get this error:

[4:DEST_ODBC:Consolidated Customer Matchcode] Data Access Plugin - Max. ODBC error count (49999) exceeded. Last error: [22018] [DataFlux][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 11. (0);[HY008] [DataFlux][ODBC SQL Server Wire Protocol driver]Operation cancelled. Error in parameter 116. (0).

I then created a new date variable and converted the character value to a date field (date DOB DOB=todate(left(DateOfBirth,10)), and mapped that to the datetimeoffset field, but I am still getting the same error. Has anyone had any experience with this, and can offer some suggestions, please?

To complicate matters further, it runs fine locally but gives that error when running on the server, so I assume it is using different ODBC drivers.

Re: Count monthly disenrollment

JHE — Mon, 31 Oct 2022 04:47:06 GMT

thank you. But if I want count 2021 disenrollment, the rest of the month dose not enrolled should be counted as disenroll, hwo could I do that

similar to this ..but it is disenroll. How to ?

https://communities.sas.com/t5/SAS-Programming/Finding-the-continous-and-discontinous-months-enrollment-over/td-p/614455

Re: Get hour and minutes of the (current time minus 20 second) into macro variables?

Astounding — Mon, 31 Oct 2022 04:51:17 GMT

Have you tried

%let now = %sysfunc(time() ) - 20;

The other statements probably figure out to perform the math without even adding %eval

Re: Count monthly disenrollment

Astounding — Mon, 31 Oct 2022 04:31:38 GMT

You don't need to change the data. You just need PROC FREQ to apply a format. For example:

proc freq data=have;
format enrollment yrmon7.;
tables member_id enrollment;
run;

Count monthly disenrollment

JHE — Mon, 31 Oct 2022 04:20:46 GMT

I need count monthly disenrollment for 2022.

Data table :

MEMBER_ID	ENROLLMENT	DISENROLLEMNT
A0001	2/1/2021	3/1/2021
A0001	5/29/2021	6/24/2021
A0001	7/1/2021	8/1/2021
B0001	6/3/2021	8/2/2021
B0001	10/1/2021	11/1/2021

How do I have the monthly disenroll , and count by member_ID: as following

1. Member_ID, Disenroll_Month_Flg

A0001 202104

A0001 202107

A0001 202209

B0001 202109

B0001 202112

2. Summary: disenroll by month

month count:

202104 1

202107 1

202109 2

202112 1

3. Summery disenroll by member:

Member Count

A0001 3

B0001 2

Re: recode catergorical variables

Astounding — Mon, 31 Oct 2022 04:20:00 GMT

Have you examined the log and the output of the program you posted? It creates a character variable DISTRICTS that does not take on numeric values like 1, 2, and 3. Yet your second DATA step seems to expect those values. So it is impossible to determine what data you have, and what result you would like. Perhaps you need statements that look more like:

bay = (districts = "bay");

That statement would at least match the sample data you posted.

assign value for a group under condition

Zhongda — Mon, 31 Oct 2022 03:54:16 GMT

Hi All,

I am new to sas and I was trying to achieve such thing:

here is the dataset:

PERMNO EVTDATE first_event inter_event

10001 8/1/11 1 0

10001 4/2/12 0 1

10002 7/6/98 1 0

10002 10/27/98 0 1

10011 11/13/95 1 0

10011 5/28/96 0 1

10011 5/29/96 0 1

10016 12/9/99 1 0

10016 5/3/00 0 1

10020 4/6/87 1 0

10020 10/21/87 0 1

10020 12/11/87 0 1

10020 4/17/90 1 0

10020 4/24/90 0 1

10028 8/17/99 1 0

10028 3/3/00 0 1

10028 7/14/06 1 0

10028 5/10/07 0 1

I would like to create another column call program and the column will state as:

PERMNO EVTDATE first_event inter_event define a program, and numbers of event in the group

10001 8/1/11 1 0 2

10001 4/2/12 0 1 2

10002 7/6/98 1 0 2

10002 10/27/98 0 1 2

10011 11/13/95 1 0 3

10011 5/28/96 0 1 3

10011 5/29/96 0 1 3

10016 12/9/99 1 0 2

10016 5/3/00 0 1 2

10020 4/6/87 1 0 3

10020 10/21/87 0 1 3

10020 12/11/87 0 1 3

10020 4/17/90 1 0 2

10020 4/24/90 0 1 2

10028 8/17/99 1 0 2

10028 3/3/00 0 1 2

10028 7/14/06 1 0 2

10028 5/10/07 0 1 2

let say for permno 10001, there are two obs and they form a program and the program has two event in a row, so it will be definded as 2 for each of the two obs.

Thank you so much for your help!

Appreciated,

Zhongda

Get hour and minutes of the (current time minus 20 second) into macro variables?

hhchenfx — Mon, 31 Oct 2022 03:36:20 GMT

Hi,

I want to put hour and minute of the time 20 second before current time into macro variable.

The code below might convey my idea better.

Can you please help?

Thanks,

HHC

%let now=%sysfunc(time()       MINUS 20 SECOND);
%let hh=%sysfunc(hour(&now),z2.);
%let mm=%sysfunc(minute(&now),z2.);

%put &hh &mm;

Re: How to export multiple datasets when using SAS Add-Ins and Excel

SASKiwi — Mon, 31 Oct 2022 03:18:31 GMT

This works for any dataset of your choosing, not just those in the SASHELP library. You can specify sending your data to a new sheet and provide the name:

Re: How to export multiple datasets when using SAS Add-Ins and Excel

carlos_m_jimenez — Mon, 31 Oct 2022 03:01:52 GMT

That was just an example since I cannot share privately owned data. The task is to export any resulting dataset to its respective tab.

Re: How to export multiple datasets when using SAS Add-Ins and Excel

SASKiwi — Mon, 31 Oct 2022 02:27:57 GMT

You don't have to code to do that. Just click on the Data icon in the SAS tab and click the Browse button. Select library SASHELP and dataset CARS and select Open. The default sheet name stays as Sheet1 but you can just change it.

Re: How to use Colon (:) to resolve a condition in Macro

SASKiwi — Mon, 31 Oct 2022 02:14:27 GMT

You can't use '=:' in macro logic. It is simply not available. You can do the same thing though like this:

%if %substr(&output,1,2) ^= SC %then %do;

How to use Colon (:) to resolve a condition in Macro

SASuserlot — Mon, 31 Oct 2022 01:49:32 GMT

I am trying to resolve the condition of the macro in my code. I have the two conditions where I want to output the error in log based on the input macro variable 'fld' . If it starts with numeric then it have to put one condition otherwise it need to put another error in log ( most of my names with 'SC' if its not numeric

In my example fld= 101-281 then I am expecting to print 'ERROR: 101281 starts with Numeric", My second one is mostly my name starts with 'SC' how can I control the second condition using the ':' colon in the % if condition to print 'ERROR:&dsn ( numeric values of the 'fld' name) starts with alpha character SC"

I am able to use ':' in dataset condition but not sure how to use it the macro, instead of writing every name in the 'If' Condtion.

Thank you for your inputs.

%macro check (fld=);
*%global output dsn;
%let output = %sysfunc(compress(&fld, "-"));


%if &output ^=: SC %then %do;
	%let dsn= %substr(&output.,1,6);

data _281;
	putlog  "ERROR: &dsn starts with Numeric";
run;

%end;

%else %if &output = :SC %then %do;
	%let dsn= %substr(&output.,3,6);
data _109;
	putlog  "ERROR:&dsn starts with alpha character SC";
run;
%end;

%put &output &dsn;


%mend;

%check (fld= 101-281);
%check (fld= 121-281);
%check (fld= SC100426);
%check (fld= SC100843);
%check (fld= SC102126);
%check (fld= SC105143);

Re: System Option for Displaying Macro Source Code on Log Only Once

Quentin — Sun, 30 Oct 2022 23:38:38 GMT

MPRINT is not showing the macro code, it's showing the SAS code that the macro generates. Each time a macro is called, it may generate different SAS code (depending on the parameters passed, the input data, global macro variables, etc etc.) So if the admins want to be able to read the log to see all the SAS code executed (which I think is reasonable, I always leave MPRINT on), I don't think an option to show the results of MPRINT once per macro called would meet that need.

Re: So it is all spam I write ... (or: What on Earth... )

Quentin — Sun, 30 Oct 2022 23:33:26 GMT

Hi,

That's got to be frustrating. Please see this post with tips:

https://communities.sas.com/t5/Community-Memo/What-to-do-when-our-spam-filter-traps-your-post/ba-p/454914

I'd suggest emailing communities@sas.com to report the overactive spam filter. The admins should be able to find your messages and un-spam them.

So it is all spam I write ... (or: What on Earth... )

fja — Sun, 30 Oct 2022 22:30:33 GMT

Dear Community!

I find my postings being silently deleted quite immediately after being posted ... 4 out of 7 today. Not only are they deleted, they vanish without a trace in the haze and are neither listed in my profile (under recent postings) nor do I get any feedback that they have been deleted for a certain reason (not speaking of mentioning the reason itself).

I would suggest to at least provide some sort of feedback in cases like that.

It is superfluous to mention that I am pretty upset with that. If you are not interested in my contributions, fair enough ... but you should at least let me know.

Kind regards.

Re: Use PROC SQL to merge more than two datasets by one common variable

SanKH1 — Sun, 30 Oct 2022 22:22:12 GMT

Thank you! I used the first solution you mentioned.

recode catergorical variables

Nyac122 — Sun, 30 Oct 2022 22:12:30 GMT

i am trying to recode my district variables so i can use proc reg and its not working .

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed

data flgrads;
input districts $ 1-9 year $ 10-20 percentage;
datalines;
alachua 2016-2017 82.7
alachua 2017-2018 88
alachua 2018-2019 88.5
alachua 2019-2020 90.4
alachua 2020-2021 86.6
baker 2016-2017 81
baker 2017-2018 75.5
baker 2018-2019 78.8
baker 2019-2020 84.5
baker 2020-2021 85.7
bay 2016-2017 78.0
bay 2017-2018 81.1
bay 2018-2019 82.5
bay 2019-2020 88.5
bay 2020-2021 90.2
bradford 2016-2017 78.9
bradford 2017-2018 89
bradford 2018-2019 87.7
bradford 2019-2020 98.2
bradford 2020-2021 85
brevard 2016-2017 85.9
brevard 2017-2018 88.1
brevard 2018-2019 88.3
brevard 2019-2020 90.3
brevard 2020-2021 90.6
broward 2016-2017 81
broward 2017-2018 84.3
broward 2018-2019 86.2
broward 2019-2020 89.4
broward 2020-2021 89.1
calhoun 2016-2017 80.9
calhoun 2017-2018 86.9
calhoun 2018-2019 87.9
calhoun 2019-2020 89.9
calhoun 2020-2021 93.1
Charlotte 2016-2017 81.0
Charlotte 2017-2018 87.6
Charlotte 2018-2019 86.4
Charlotte 2019-2020 90.4
Charlotte 2020-2021 90.9
Citrus 2016-2017 78.9
Citrus 2017-2018 84.1
Citrus 2018-2019 86.0
Citrus 2019-2020 87.1
Citrus 2020-2021 88.1
Clay 2016-2017 88.4
Clay 2017-2018 91.1
Clay 2018-2019 91.9
Clay 2019-2020 93.4
Clay 2020-2021 92.7
Collier 2016-2017 88.2
Collier 2017-2018 91.9
Collier 2018-2019 91.9
Collier 2019-2020 92.2
Collier 2020-2021 92.7
Columbia 2016-2017 70.7
Columbia 2017-2018 88.4
Columbia 2018-2019 92.4
Columbia 2019-2020 95.4
Columbia 2020-2021 95.6
MiamiDade 2016-2017 80.7
MiamiDade 2017-2018 85.4
MiamiDade 2018-2019 85.6
MiamiDade 2019-2020 89.6
MiamiDade 2020-2021 90.1
DeSoto 2016-2017 63.8
DeSoto 2017-2018 60.9
DeSoto 2018-2019 71.3
DeSoto 2019-2020 84.6
DeSoto 2020-2021 82
Dixie 2016-2017 89.5
Dixie 2017-2018 96.9
Dixie 2018-2019 90.6
Dixie 2019-2020 89.8
Dixie 2020-2021 84
Duval 2016-2017 80.8
Duval 2017-2018 85.1
Duval 2018-2019 86.5
Duval 2019-2020 90.2
Duval 2020-2021 89.6
;
proc print;
run;
data flgrads2;
set flgrads;
Alachua= (district= 0);
Baker= (districts= 1);
Bay = (districts= 2);
Bradford= (districts= 3);
Brevard= (districts= 4);
Broward= (districts= 5);
Calhoun= (districts= 6);
Charlotte= (districts= 7);
Citrus= (districts= 8);
Clay= (districts= 9);
Collier= (districts= 10);
Columbia= (districts= 11);
MiamiDade= (districts= 12);
DeSoto= (districts= 13);
Dixie= (districts= 14);
Duval= (districts= 15);
run;

System Option for Displaying Macro Source Code on Log Only Once

F_Pierantoni — Sun, 30 Oct 2022 22:02:34 GMT

Due to cybersecurity restrictions, our SAS Site Administrators have enforced system options like SOURCE, SOURCE2 and MPRINT on every SAS session to make sure that every single line of submitted code is printed on the SAS Log.
Options SOURCE and SOURCE2 pose no particular problem. But a locked-in MPRINT option has the unwanted effect of multiplying the number of lines of Macro Code being printed on the Log, every time a Macro is invoked.
Having a system option that displays the Macro Code only once (regardless of how many times the Macro is called in the program) would be very helpful to reduce the number of lines displayed on the Log.
MPRINT is a debugging option that works well as it does.
The new option could be named somewhat differently (maybe MLOG ?).