kcskaiser Tracker

Re: Get the name of the file an observation came from when the filename has multiple files

kcskaiser — Thu, 03 Dec 2015 14:51:14 GMT

I got it to work like you see below. I get a lost card note in the log which I can't yet explain but I'm looking at it now.

If anyone sees a cleaner solution please let me know. And thank you everyone for the input.

%let folder = \\kcpublic\PUBLIC\Public\SAS\Costing Run Logs\Costing Run Logs 09-10\ ;
filename indsn "&folder";
data one;
infile datalines truncover;
length fil2read $150;
input fil2read $char150.;
fil2read = cats("&folder",fil2read);
infile dummy filevar = fil2read end = done;
file_name = fil2read;
do while(not done);
input @1 intext $char100.;
output;
end;
datalines;
Empty Model Prelim Project Log.txt
Empty Model Previous Project Log.txt
Profman Containers to Cars Marketing Prelim Ordered List Project Log.txt
Profman Containers to Cars Marketing Previous Ordered List Project Log.txt
Profman Containers to Cars Prelim Ordered List Project Log.txt
Profman Containers to Cars Previous Ordered List Project Log.txt
Profman Output Marketing Prelim Data to SAS Prm Stg Project Log.txt
Profman Output to SAS PRM STG Prelim Project Log.txt
Profman Output to SAS Prm Stg Previous Project Log.txt
;
run;

Re: Get the name of the file an observation came from when the filename has multiple files

kcskaiser — Wed, 02 Dec 2015 22:43:20 GMT

A complication is tht the file names have spaces in them. So for example one of them is

Empty Model Prelim

Should I enclose the names in tics (") to start with or how do I handle that?

I tried surrounding the names with ticks, that didn't work. Other ideas?

Re: Get the name of the file an observation came from when the filename has multiple files

kcskaiser — Wed, 02 Dec 2015 21:00:36 GMT

I'm OK with being wrong. But I don't see how to do it.

Re: Get the name of the file an observation came from when the filename has multiple files

kcskaiser — Wed, 02 Dec 2015 20:44:00 GMT

Correct me if I'm wrong but doesn't processing stop at the end of the smallest input file? Some of mine are long some are short but they are all different sizes.

Get the name of the file an observation came from when the filename has multiple files

kcskaiser — Wed, 02 Dec 2015 20:36:04 GMT

I have a filename that has multiple files listed in it, like this example;

filename filesin ("%CMPRES(&folder\First File.txt)" "%CMPRES(&folder\Second File.txt)" "%CMPRES(&folder\Third File.txt)");

Then my datastep;

data allset;

infile filesin;

input @1 intext $200.;

run;

The question is how can I set a variable in the datastep that contains the name of the input file?

* I found I needed the %CMPRES to allow Windows to get the correct name.

Re: Causal Relationship Study using Enterprise Guide

kcskaiser — Tue, 20 Oct 2015 15:14:50 GMT

This is where I run into a problem. I think proc fastclus might be what I need to use but I don't understand the how. But you got me on the right track in my thinking and I've revised my requirments a bit.

I need to study form usage across two different time frames. The time frames are Day and Night devided by 6am and 6pm. I'll call that variable tms and make day = 1 and night = 2;

Do I then need to convert each form into a number? Form A becomes 1, Form B becomes 2, etc. and then do the same with each role? Role A becomes 1 and Role B becomes 2 out to the 713 roles in play?

Then run;

proc fastclus data = closebyformrole maxc = 713 maxiter=100 out = clus;

var tms role_num form_num;

run;

This produces some output but I can't pretend I know what it all means. One part however looks intreguing to me. Across the top is the role_num 1 to 713 and down the side is 1 to 24, then each role_num has what looks like 1 or more percentages(?) in it. But maybe not because I really don't know what I'm looking at.

I know I can't expect someone to teach me about statistics here or even clustering but some tiny guidence would be greatly appreciated.

Thanks.

Causal Relationship Study using Enterprise Guide

kcskaiser — Mon, 19 Oct 2015 19:10:59 GMT

Other than ABM and ProfMan we have only what you can find in base SAS and E.G. I've been asked to find a relationship in some of the data we collect on form usage by role within the company. So my data set is compreised of about 3 million rows consisting of a date/timestamp, a form name and a role (user). We have about 700 unique forms and many thousands of users (roles) that use them.

I admiit this was a new request for me, I've not done this kind of study before. I've used this same data to analyze usage by date and day of week, and hour and by role and form name and all the basic kinds of analysis you might think of. But finding a corralation between one form and another by role is not sometihing I've done and really don't know where to start.

He likens his request to a grocery store where related products might be grouped together. For example nacho chips would have salsa, and cheese dip on a nearby shelf. So he would like to know what is on the shlef next to Form A, and what else is used near any given Form.

I picture a cloud (the role) filled with forms that they use. This is easy, but how do I say Form A is tighly matched with Form B? And then extend that by saying Form B is also closly matched to Form Z?

Most of the examples I see on this and other sites seem well above my understanding. So I'm hoping a simple step by step will help me better understand how to get started. Always keeping in mind I don't have access to many of the really high-end analysis procedures that might make this much easier.

Thoughts?

Annotating HBAR SubGroups in the bar and at the end

kcskaiser — Tue, 15 Sep 2015 15:50:09 GMT

I have an HBAR GCHART that is currently annoted with count values centered inside the bar which is subgrouped by month. Kind of like this example.Each bar is a day of the week... each subdivision a month.

==========================================================================================

| 400 | 900 | 3000 | 500 |

==========================================================================================

To do this I created a middle data set and calculated the center and values for each of the amounts. What I would like to do now is add the total to the end of the bar, outside the bar. | 4800 for example.

But I can't figure out how to do this. My current program (without data) is attached.

Dealing with server and workstation patches

kcskaiser — Tue, 07 Jul 2015 14:50:25 GMT

Lately we have had a lot of discussions internally about how and when to apply patches and other maintenance to both our SAS Servers and windows workstations. We have a 24/7 shop in the sense that jobs (primarily SAS/E.G.) could be running at any time of the day. These jobs often run 12+ hours and are generally contained in Ordered Lists. Scheduling an OL is anything but easy in SAS v9.2 so at the moment all are being run by the user as the need arises.

This of course means that if IT decides to upgrade/patch/reboot his workstation the connection is lost and the jobs are killed. Same is true of course for the servers but those we usually have a warning about before the patch is done.

How is your company handling and scheduling these very necessary but troublesome windows patches? The more details I can gather the easier it will be for me to communicate options that maybe IT hasn't thought of, so please be specific. I'm pushing for an upgrade of SAS and moving it to Red Hat Linux, you're impressions about this same issue here would be welcome also.

Thanks in advance for any ideas/thoughts you have.

Re: Using Proc SQL to delete rows with a where and having clause

kcskaiser — Fri, 20 Feb 2015 16:46:19 GMT

Hmmm, very interesting approach. After adding a 'datalines' on your input I was able to reproduce your output. Additionally your solution isn't working correctly anyway because you should end up with just the first row (min(a2)=10), not three or four (min(a2) ge 10). I get the same warning and like you it concerns me.

My table is a bit more complex so my actual code would need to look something like this:

proc sql;

delete from cp_tariff_dim_backup a

where a.TARIFF_INITIAL = 'NS'

and a.TARIFF_ITEM = '0010100000'

and a.TARIFF_NBR = '00025'

and a.SRC_SYS = 'KCS'

and a.TARIFF_ISS_ID = 'NSPQ'

in (select tariff_dim_key from

(select b.tariff_dim_key, min(tariff_dim_key) as min_dim

from cp_tariff_dim_backup b

group by b.tariff_dim_key

having min(a.tariff_dim_key) <> b.tariff_dim_key))

;

quit;

Which gives me an 'ERROR 22-322: Syntax error..'.

Oddly enough (at least I find it odd) if you surround the condition checks in the where clause with parens () the syntax error goes away but no rows are deleted.

where (a.TARIFF_INITIAL = 'NS'

and a.TARIFF_ITEM = '0010100000'

and a.TARIFF_NBR = '00025'

and a.SRC_SYS = 'KCS'

and a.TARIFF_ISS_ID = 'NSPQ')

Thoughts?

Using Proc SQL to delete rows with a where and having clause

kcskaiser — Thu, 19 Feb 2015 21:31:22 GMT

What I'd like to work is this:

proc sql;

delete from odbclib.cp_tariff_dim

where tariff_initial = 'NS'

and tariff_item = '0010100000;

having min(tariff_dim_key) ne tariff_dim_key

; quit;

This code works perfectly to create a data set as in select * from etc.

It creates a data set of all the rows I want to delete. But if I run the above I get an "ERROR 22-322: Syntax error...".

Is there a way to use a 'having' clause with the where in order to delete rows from a SQL Server table?

Re: Call Define to turn an entire row a color

kcskaiser — Tue, 27 Jan 2015 14:52:34 GMT

This worked.. sort of. I thought I'd do it in a loop counting columns 1 to 13. This sounded promising until I tried it. The number of columns internal to SAS is apparently set by your columns statement not what is actually on the report. So it became 1 to 21. Using higher numbers causes all kinds of strange errors.

I can't get some columns to turn red no matter what I do. In my code they are time, successful_rows, and last_rows I have no idea why. Unless it has to do with them already being set to a color in a previous compute block. But that doesn't explain why it does override the color in the first column.

My compute block now looks like below:

compute last_error_code;

if last_error_code gt 0 then do i = 1 to 21;

call define(i, "style/merge", "style=[background=red

font_weight=bold]");

end;

endcomp;

All but three columns are now red, better but not right.

Thanks for this response it did help a lot.

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 21:01:53 GMT

This does indeed seem to be what is happening. But surly SAS has a way around this.

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 20:40:53 GMT

This is exactly what I want to happen. And I'm guessing your right about something else blocking it. I've included all the code from my proc report.

Thanks.

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 20:18:53 GMT

This is the full code of my 'Proc Report'.

proc report data = logs3

(where = (datepart(actual_start) ge &runmonth))

style(summary)=[background=yellow]

nowd split = '*' headskip;

column workflow_name id2 id

actual_start actual_start=startmin

actual_start2

session_timestamp session_timestamp=endmax

duration time

last_duration avg_duration

successful_rows last_rows

diff_from_last_rows avg_rows

row_diff_from_avg pct_diff_from_avg FAILED_SOURCE_ROWS

next_place last_error_code

;

define workflow_name / display noprint;

define id2 / group noprint;

define startmin / min f=datetime16. '*Start Time';

define endmax / max f=datetime16. '*End Time';;

define id / display style = [background=lightgreen

flyover=$idname.] '';

define actual_start / order noprint;

define actual_start2 / computed noprint f= datetime16. '*Start Time';

define session_timestamp / display noprint f= datetime16. '*End Time';

define duration / analysis noprint f= time12.2 '*Run Time';;

define time / computed f= time12.2 '*Run Time';

define last_duration / display f= time12.2 'Previous*Run Time';

define avg_duration / display f= time12.2 'Average*Run Time';

define successful_rows / analysis f= comma12. 'Rows*Processed';

define last_rows / analysis f= comma12. 'Rows*Last Month';

define diff_from_last_rows / analysis f= comma12. 'Diff From*Last Month';

define avg_rows / display f= comma12. 'Average*Rows';

define row_diff_from_avg / display noprint f= comma12. 'Diff From*Avg Month';

define pct_diff_from_avg / display f= percent6.2 '% Diff of*Avg Month';

define next_place / display noprint;

define failed_source_rows / analysis f= comma12. 'Failed*Rows';

define last_error_code / display 'Error*Code';

break after id2 / page summarize;

* Because I want to show all values, even those equal to the ;

* previous value, I used this: ;

* http://support.sas.com/kb/24/322.html ;

compute actual_start2;

if actual_start ne . then hold = actual_start;

actual_start2 = hold;

endcomp;

compute before ;

alltime = 0;

endcomp;

compute before _page_ /

style = {just=l FONTWEIGHT=bold color=blue

backgroundcolor=palegreen};

txt = upcase(compbl(cat(workflow_name,' --> ',scan(id2,2,' '))));

if workflow_name = scan(id2,2,' ') then txt = upcase(workflow_name);

line txt $75.;

endcomp;

compute id;

if id in ('A1','C10','D1','J7','K27','L6','Q_R','U15'

'W6A','W6B') then

call define(_row_, "style",

"style=[backgroundcolor=lightgrey]");

endcomp;

compute time;

time = endmax - startmin;

call define(_col_, "style", "style=[background=lightblue

font_weight=bold]");

endcomp;

compute pct_diff_from_avg;

call define(_col_, "style", "style=[color=tlight.

font_weight=bold]");

endcomp;

compute diff_from_last_rows;

call define(_col_, "style", "style=[color=tlight.

font_weight=bold]");

endcomp;

compute successful_rows;

call define(_col_, "style", "style=[background=wheat

font_weight=bold]");

endcomp;

compute last_rows;

call define(_col_, "style", "style=[background=KHAKI

font_weight=bold]");

endcomp;

compute after _page_ /

style = {just=l FONTWEIGHT=bold color=blue

backgroundcolor=white};

txt = cat('Next Up: ',next_place);

line txt $175.;

endcomp;

compute after id2;

*call define(_row_,"style", " style = [background=yellow]");

tottime = endmax-startmin;

days = floor((tottime)/(24*60*60));

hours = hour(tottime);

minutes = minute(tottime);

seconds = second(tottime);

theline = compbl(cat(put(days,z2.),' days ',

put(hours,z2.),' hours ',put(minutes,z2.),' minutes ',

put(seconds,z2.),' seconds'));

alltime = alltime+tottime;

adays = floor((alltime)/(24*60*60));

ahours = hour(alltime);

aminutes = minute(alltime);

aseconds = second(alltime);

thealine = compbl(cat(put(adays,z2.),' days ',

put(ahours,z2.),' hours ',put(aminutes,z2.),' minutes ',

put(aseconds,z2.),' seconds'));

line @1 'Group Time: ' +3 theline $char75.;

line @1 'Total Time: ' +4 thealine $char75.;

endcomp;

compute last_error_code;

if last_error_code gt 0 then do;

call define(_row_, "style/merge", "style=[background=errcol.

font_weight=bold]");

end;

endcomp;

run;

Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 18:02:37 GMT

The last column in my proc report is an error code value. If the error code is gt than 0 then I want then entire row set to a background color of red.

This is my define:

define last_error_code

/ display

'Error*Code';

This is my compute block:

compute last_error_code;

if last_error_code gt 0 then do;

call define(_row_, "style/merge", "style=[background=errcol. font_weight=bold]");

end;

endcomp;

The errcol. format is just white or red.

The problem is that only the the error code column and a couple of other columns are made red. Some stay the same color they were, others lose color completely, some turn a different color altogether. The style/merge is my latest attempt, I didn't expect it to work and of course it doesn't.

Several other rows have compute blocks using call defito set style values, several columns also have compute blocks that also use call define to set style values.

This one is simple.. how can I turn the entire row red regardless of what else is being done?

Thanks in advance everyone

Re: Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 21:02:26 GMT

This worked.

1) I removed the alias for rowstot

2) I removed the define for rowstot

3) I modified my compute after id2 block by removing any reference to rowstot

4) I added a: break after id2 / page summarize;

I did not know I could do step 4 until I tried it at your suggestion. I thought it had to be done in a rbreak, which didn't work.

Thank you so much for the help.

Re: Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 17:51:01 GMT

This would work, thank you. But it means that in some cases a variable whose value is only 2 bytes wide will take up 35 bytes of space. One of the variables to the left varies from 2 to 35 wide.

Good answer but in this case it won't work without having to do a lot of if/then coding.

I'd rather be able to easily emulate what rbreak after / summerize does.

Thank you.

Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 17:08:11 GMT

I'm doing my best to make this understandable.

SAS v9.2, E.G. v4.3

Data is sorted by id2.

Very simplified my code looks like this:

proc report data = logs3 nowd;

column id2

rows

rows=rowstot ;

define id2 / group noprint;

define rows / analysis;

define rowstot / sum noprint;

break after id2 / page;

compute after id2;

line @40 rowstot comma12.;

endcomp;

I want to add a summery line at the bottom of each of my groups. I can do this with a <compute after id2> block but then I can't guarentee the calculated variables (sum of row counts) used in the <line> will line up correctly with the column its reflecting. This is because rows to its left may be of different lengths.

I tried to use <rbreak after / summerize skip dol> but I have no control over the look of the by variable (id2). And the by line is ugly it might say something like <id2=BI long name here> when all I really want it to say is <long name here>.

What do I need to do to add the sum of my rows column to a new row broke at each id2 variable?

Thanks everyone.

Re: ABM v7.1 Workspace Manager

kcskaiser — Wed, 29 Oct 2014 21:47:18 GMT

I don't completely understand your answer in its entirety but I do understand the "Yes it is normal to do that" part.

I'll study your response a bit more to better understand your point. But in the mean time will proceed on setting up a test environment for ABM.

Thank you.