dsbihill Tracker

Plot options Des= not working

dsbihill — Thu, 04 Aug 2016 02:27:19 GMT

I can not get my proc gplot to eliminate the auto generated title so that i can replace it.

Current Output

I am trying to replace the highlighted title with a custom title but my code doesnt seem to work like i think it is supposed to.

This is what I have right now

PROC GPLOT DATA = WORK.SORTTempTableSorted NOCACHE;

PLOT trans_qty * monyear Mfreq * monyear  / 
	des='APG = #Byval3, Usage Type = #Byval4'
	OVERLAY 
	VAXIS=AXIS1
	HAXIS=AXIS2
	FRAME
	LEGEND=LEGEND1;

	BY customer gd apg1 usage_type;

RUN;

I cant figure out what I am doing wrong here.

Thank you

Re: COMPRESS function not working

dsbihill — Wed, 20 Apr 2016 14:38:14 GMT

need to tell it to remove spaces, Try this. Also you should use quit, not run to end a proc SQL.

proc sql;
CREATE TABLE xxx AS
	SELECT
 		A AS CommDesc
		,COMPRESS('0802 12',' ') AS CN
		;
Quit;

Proc Anova ods output

dsbihill — Tue, 12 Jan 2016 14:29:16 GMT

Hello all, Thanks in advance for the help here

I have this proc anova code that appears to be working properly(validations still to come). But I need to output the p-values from this to a table based on the by groups. The ods output system still confuses me and I have been unsuccessful getting what I need. How do i get that output?

proc anova data=tmp_seasonal_raw(where=(testType='Anova'));
	class month;
	model total_qty=month;
	by customer gd apg1;
	MEANS month / HOVTEST=BARTLETT;

run;

Question about consecutive variables

dsbihill — Tue, 03 Nov 2015 00:52:04 GMT

I am looking for a way to compare multiple observations and output a file that contains the final number from multiple part number change observations.

The data haveSorted below i have ordered only for ease of seeing how the part numbers are stored. The actual data I have is not sorted in any way.

Any recommendations would be greatly appreciated as I have been unable to think of a method to do this.

DATA haveSorted;/*Manually sorted for ease of understanding*/
infile datalines delimiter=",";
input prev_part $ part_sup $;
datalines;
.,X1
X1,P2
P2,P5
P5,D3
.,4C3
4C3,7S2
7S2,6BW
.,5S2
.,6R1
6R1,3F3
3F3,24X
;


DATA have;
infile datalines delimiter=",";
input prev_part $ part_sup $;
datalines;
.,X1
X1,P2
3F3,24X
P2,P5
4C3,7S2
P5,D3
.,4C3
7S2,6BW
.,5S2
.,6R1
6R1,3F3
;


data want;
infile datalines delimiter=",";
input prev_part $ part_sup $ final_part $;
datalines;
.,X1,D3
X1,P2,D3
P2,P5,D3
P5,D3,D3
.,4C3,6BW
4C3,7S2,6BW
7S2,6BW,6BW
.,5S2,5S2
.,6R1,24X
6R1,3F3,24X
3F3,24X,24X
;

Re: Libname - Unable to startup in the beginning.

dsbihill — Thu, 22 Oct 2015 13:22:24 GMT

Put your libname statement into a process flow called Autoexec, and it should run every time you open your project.

Re: replicate value (easy question)

dsbihill — Wed, 21 Oct 2015 17:40:39 GMT

data want;
   set have;

   retain prevField;
   if field ne "" then prevField = field;
   if field = "" then field = prevField;

run;

Re: Changing Character Dates to Numeric Dates: Different Character Date Formats in One Var

dsbihill — Mon, 19 Oct 2015 17:07:19 GMT

Im sure there are more efficent ways then what i have done here, but this was a fun challenge to try out.

here is what i did

data have;
	  length start_date end_date $ 12.;
      input Start_Date $ End_Date $;
      datalines ;


6/23/2015 7/20/2015 
7/14/2015 9/5/2015 
8/4/2015 TBD 
8/20/2015 11/2/2015 
8/27/2015 TBD 
9-Feb 1-Jul 
15-Jan 1-Jul 
15-Jan 1-Jul 
3-Feb 1-Jul 
9-Feb 1-Jul 
1-Jul TBD 
1-Jul TBD 
8/27/2015 TBD 
10/6/2015 TBD 
10/13/2015 11/2/2015 
10/13/2015 TBD 
3/31/2015 5/19/2015 
5/21/2015 On Going 
6/9/2015 7/6/2015 
7/8/2015 TBD 
10/20/2015 11/2/2015 
8/5/2015 TBD 
7/30/2015 11/2/2015
8/11/2015 TBD
8/18/2015 11/2/2015
8/25/2015 9/29/2015
9/10/2015 10/5/2015
18-Sep 1-Oct
18-Sep 1-Oct
9/29/2015 11/2/2015
8/4/2015 9/29/2015
8/11/2015 TBD
8/13/2015 TBD
7/8/2015 10/6/2015
8/5/2015 ongoing
10/1/2015 11/2/2015
;


data want;
	set have;
	format new_start_date new_end_date mmddyy10.;
	
	if notdigit(start_date) ne 1 then do;
		if length(trim(left(start_date))) <= 6 then
			new_start_date = input(compress(start_date || "-" || year(date())), date11.);
		if length(trim(left(start_date))) > 6 then
			new_start_date = input(start_date, mmddyy10.);
	end;
	
	if notdigit(end_date) ne 1 then do;
		if length(trim(left(end_date))) <= 6 then
			new_end_date = input(compress(end_date || "-" || year(date())), date11.);
		if length(trim(left(end_date))) > 6 then
			new_end_date = input(end_date, mmddyy10.);
	end;
	else status = end_date;

	drop start_Date end_Date;

run;

Re: PROC SQL: can you replace missing value(s)??

dsbihill — Thu, 15 Oct 2015 15:15:55 GMT

If statements dont function in proc sql but you can do either of the following.

Case
      when obs1 = . then 0
      else obs1
end as obs1

or you can simply do this

sum(obs1, 0) as obs1

Re: How to sum up one column while keeping the others constant

dsbihill — Tue, 13 Oct 2015 21:18:45 GMT

Can you post your code or the part of the log above the error? You probably have an extra comma somewhere.

Re: How to sum up one column while keeping the others constant

dsbihill — Tue, 13 Oct 2015 20:54:55 GMT

Proc SQL;
    create table want as
         select State, city, food, sum(eligible2) as eligible
            from havetable
     group by state, city, food
;
quit;

Something like this?

Re: Shorten long string, but ensure it remains unique

dsbihill — Thu, 06 Aug 2015 17:20:33 GMT

Why do you need to convert this dataset from a long to a wide set?

Re: Help with Tools

dsbihill — Tue, 07 Jul 2015 19:30:00 GMT

Yes many parts do have actual seasonality. Is there a statistical test that can be used to Validate this?

Output i am looking for would look like this

Part#	Year	Month	ActualQuantity	Expected Quantity
5d3423	2015	1	70	80
5d3423	2015	2	65	85
5d3423	2015	3	45	40
5d3423	2015	4	25	35
5d3423	2015	5	15	30
5d3423	2015	6	15	10
5d3423	2015	7	0	5
5d3423	2015	8	0	5
5d3423	2015	9	0	10
5d3423	2015	10	0	25
5d3423	2015	11	0	30
5d3423	2015	12	0	45
				400	Total expected for 2015 based on past trends

So basically I need to take a 1 year estimated Number and generate the properly weighted values by month based on the past trend data. This i hope will allow me to see if I am out of the box on the estimated numbers early in the year. I am not a statistician but do have a good understanding of sas programing and I am really looking for Ideas on what tools i need and the process to go through to get to this end result.

Thank you so much for all your time.

Re: Help with Tools

dsbihill — Tue, 07 Jul 2015 17:52:58 GMT

Sorry I know i am not explaining this well. Let me try again.

From my first data set there is many years and about a million records. I want to figure out from all that previous history by part number and month an average trend across the year. I then want to use that trend to change my second data set from a yearly total into a monthly trend based on past history of that part. I need to do that so I can compare my expected usage to my actual usage and account for the trends of previous history.

I hope this helps

Thank you

Help with Tools

dsbihill — Tue, 07 Jul 2015 17:33:45 GMT

I am looking for a way in sas to look at component changes over time.

Have:

- data set that contains material_used quantities with date from 2011-2015

data set looks like this:

Unit#, Date, Part#, Qty_used

345 01jan2014 5d3423 5

2034 23feb2015 6t5453 10

to many years of data

- data set that contains Expected 2015 usage(but is total for the year)

dataset looks like

Year, Part#, Expected_Qty_used

2015 5d3423 400

2015 6t5453 700

I would like to take the trend data by month from the first data set and then figure out from previous usages how many i think i should use in the current year based on the previous years.

Is there a tool or method in sas that could do this for me?

If not are there any suggestions on how i go about this?

Thanks

Re: insert rows based on output from subtracting two rows in the same column

dsbihill — Wed, 17 Jun 2015 03:55:48 GMT

I am no SAS expert and i'm sure the real experts on this sight can make this better. I would love some suggestions but this is what i came up with. There is currently no logic to increment your email numbers up but i wasn't sure that was necessary

proc format ;

value formula

low - 15 =0

16-40 =1

41-70=2

71-100=3

101-140=4

141-175=5

176-225=6

226-300=7

301-high=8

;

run;

data have;

input ID Email $ type $ edate mmddyy10. ;

format edate mmddyy10.;

datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 Sdate 10/05/2015

1 4 Sdate 11/09/2015

1 5 Sdate 12/11/2015

2 1 rdate 06/21/2015

2 2 rdate 07/05/2015

2 3 rdate 07/09/2015

2 4 Sdate 06/21/2016

2 5 Sdate 07/05/2016

2 6 Sdate 07/09/2016

;

run;

/*Sort have dataset*/

proc sort data=have;

by id email type edate;

run;

data want;

/*merge have dataset to itself with on incremented forward on record*/

merge have

have(firstobs=2 rename=(id=_nxt_id email=_nxt_email

type=_nxt_type edate=_nxt_edate));

/*test if current and next record are same id and expected types*/

if id=_nxt_id and type = 'rdate' and _nxt_type = 'Sdate' then do;

output; /*ouput rdate observations*/

/*calculate the interval between the rdate and sdate records*/

/*then get formula count from the format*/

_interval = intck('days', edate, _nxt_edate);

_form_cnt = put(_interval, formula.);

type = 'formula';

/*loop through and create formula observations based on above calc's*/

do i=1 to _form_cnt;

edate = intnx('days',edate, _interval/(_form_cnt+1));

output; /*output formula observations*/

end;

else output; /*output the Sdate observations*/

drop _: i; /*drop temp variables and loop variable*/

run;

Re: re: Scan function

dsbihill — Mon, 15 Jun 2015 16:38:48 GMT

If you are trying to handle both situations of 3 letters and no letters why not change your above code to something like this

PROC SQL NOPRINT;

SELECT DISTINCT ID

INTO :IDLIST1 SEPARATED BY ','

FROM have;

QUIT;

and then use on the macro side

%scan(&idlist1, i, ',')

Re: re: Scan function

dsbihill — Mon, 15 Jun 2015 15:07:28 GMT

If i understand your question properly this is how i would do it. Im sure there are more elegant ways to do this.

Thanks

data have;

input id $50.;

datalines;

CME 1199 DLS 1213 WRF 1155 RTY 1345

;

run;

data want;

set have;

do i=1 to countw(id) by 2;

sep_id = catx(' ',scan(id,i), scan(id, i+1));

output;

end;

drop id i;

run;

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 20:21:03 GMT

Once i put the top level query into a sub query it all worked great

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 16:24:43 GMT

To the best of my knowledge, The having statement occurs after all calculations and the CALCULATED statement is used inside the select statement if you use a calculated field in another expression to prevent the system from having to perform it more then once.

Re: Proq SQL question

dsbihill — Mon, 11 May 2015 15:52:32 GMT

Here is the full non working code. If im understanding you correctly then, I will have to set both up as a sub query or build separate tables before i do the join?

proc sql;

create table tmp_mat_sorted as

select customer, gd, apg1,

case

when golden_usage_type = 'AA' then 'AA'

else finance_contract_name

end as finance_contract_name,

case /*create period data groups*/

when "&min_date"d < datepart(transaction_date) <= "&max_date"d then 'Add_Period'

when INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= INTNX('YEAR', "&min_date"d,-2,'S') then 'Drop_Period'

else 'Overlap'

end as period,

golden_usage_type as usage_type, part_number,

transaction_date, transaction_quantity, loco_count, yield

from dfrin.tbl_material_txn_raw u

Left join (select distinct customer, finance_contract_name,

avg(no_of_locomotives) as loco_count, yield,

case

when &add_start < fiscal_month <= &add_end then 'Add_Period'

when &drop_start < fiscal_month <= &drop_end then 'Drop_Period'

when &olap_start < fiscal_month <= &olap_end then 'Overlap'

end as period

from tmp_loco_counts

natural left join dfrin.tbl_yield_rates

group by customer, finance_contract_name, yield, period

having period ne '') c

on u.customer=c.customer and u.finance_contract_name=c.finance_contract_name and

u.period=c.period

where INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= "&max_date"d and

%_eg_WhereParam(CUSTOMER, Customer, IN, TYPE=S, IS_EXPLICIT=0 ) and

%_eg_WhereParam(GD, GD, IN, TYPE=S, IS_EXPLICIT=0 ) and

%_eg_WhereParam(usage_type,usage_type, IN, TYPE=S, IS_EXPLICIT=0 )

order by customer, gd, apg1, finance_contract_name, usage_type, part_number, period

;

quit;