About Sir_Highbury

Sir_Highbury · ‎08-24-2016

Dear experts, givend the following fata: data test; input segment: $12. jan: 10. feb: 10. mar: 10.; datalines; A 150 200 155 B 170 150 200 ; and given the following output: proc sql; create table test2 as select segment, sum(feb) as sum_feb from test group by 1 ;quit; I would like to get the same using the following reference: %let var=feb; proc sql; create table test3 as select segment, sum('&var') as sum_&var from test group by 1 ;quit; but as you can see it does not work. Any ideas? Thanks, SH.

Sir_Highbury · ‎08-04-2016

again?! please refer to the data set have1 and the data set want of my initial post! I got what I wanted and I did it on my own.. 😉

Sir_Highbury · ‎08-04-2016

well... it seems that there is not anything better than this funny proc compare: I will try to figure out how. Either there are not better, more elegant and compact solutions or everyone ignore them.

Sir_Highbury · ‎08-04-2016

Dear Kurt, thanks for your suggestion, I think that you really got the poin but I still have some concerns due to the fact that you did not apply the code on the test case I provided and when I apply your code to the my test case: data _null_; set have1 end = done; if _n_ = 1 then call execute(' proc sql; create table want as select "delta" as reference_period, t0.segment, '); call execute('(t0.'!!name!!'-t1.'!!name!!') as '!!name!!' '); if not done then call execute(','); else call execute(" from have1 t0 inner join have1 t1 on t0.SEGMENT=t1.SEGMENT where t0.reference_period in ('jan') and t1.reference_period in ('feb') ; quit; "); run; I did not get what I expect. Any suggestion?

Sir_Highbury · ‎08-04-2016

Dear Reeza, I looked at the proc compare but I did not find anything that makes what I want, i.e. an ease calculation of the difference between two data base where the variables are numeric. Are you sure that the proc compare does it? Did you ever try it or are you gessing? I would like to be sure that playing with this proc compare is not a wast of time. Unfortunately due to some restriction I cannot integrate r in SAS and therefore I have to stick to the sas procedures.

Sir_Highbury · ‎08-04-2016

dear RW9, thanks for your attention, here some clarifications: - I highlighted in bold what is my crucial point, I would like to automatize this part, not just for revenue and expenses but for n variables, my issue is not december! If jan and feb is misleading you can overwrite with peaches and lemons or x and y, the point is that I would like to subtract all the numeric variables of one data set to the ones of another one, assuming that both have an identical structure (exactly the same variables). E.g. like a matrix operation in python. - It does not make a difference if I have two data set with the identical structure or two data sets with the identical structure appended with two different key to distinguish the two of them. You can consider your data set or what you got as have1 running my script as the starting point, as you wish. Still my point is not to write my have1 in a better way but how to get from it to want, i.e. to automatize what I highlighted in bold. Thanks again for your support. BRs, SH.

Sir_Highbury · ‎08-04-2016

Dear experts, I would like to calculate the delta for all the numeric variable in 2 dataset with exactly the same structure. Input data: data have1; input reference_period: $12. segment: $12. revenue: comma5. expenses: comma5.; datalines; jan low 1000 500 jan medium 1500 600 jan high 2000 800 ; data have2; input reference_period: $12. segment: $12. revenue: comma5. expenses: comma5.; datalines; feb low 1100 550 feb medium 1400 650 feb high 2100 750 ; proc append base=have1 data=have2 ;run; what I would like to get, in bold the part that should be automatized (i.e. it is just a semplification, I have a lot of variables to compare) proc sql; create table want as select "delta" as reference_period, t0.segment, (t0.revenue-t1.revenue) as revenue, (t0.expenses-t1.expenses) as expenses from have1 t0 inner join have1 t1 on t0.SEGMENT=t1.SEGMENT where t0.reference_period in ("feb") and t1.reference_period in ("jan") ;quit; thanks in advance. BRs, SH

Sir_Highbury · ‎08-02-2016

Dear experts, here my input data: 1. a data set where for each id I specify the time period with begin and end (YYYMM) data T00_00_PTF; input reference_period: $12. begin: comma5. end: comma5.; datalines; current_month 201606 201606 previous_month 201605 201605 previous_12m 201507 201606 previous_36m 201307 201606 ; data T00_00_PTF; set T00_00_PTF; id=cats(reference_period,"_",begin,"_",end) ;run; 2. a data set of row data where the field date define the YYYYMM: data have; input date: comma5. revenue: comma5. shop: $12. country: $2.; datalines; 201606 10000 ABC UK 201606 20000 A JP 201605 30000 A JP 201508 40000 A JP 201605 30000 BC UK 201308 20000 BC UK ; on the base of the table T00_00_ptf I would like create another data set have which is expanding the initial data set have, adding to each observation the field id from the data set T00_00_ptf in the case that the variable data is in the time range. Here the output that I expect for the first observation (date=201606 => included in all the subset defined in the table T00_00_PTF) data want; input id: $25. date: comma5. revenue: comma5. shop: $12. country: $2.; datalines; current_mont_201606_201606 201606 10000 ABC UK previous_mon_201605_201605 201606 10000 ABC UK previous_12m_201507_201606 201606 10000 ABC UK previous_36m_201307_201606 201606 10000 ABC UK ; Any suggestion? I tried using a proc sql with the left outer join (left outer join T00_00_ptf t5. on (t5.begin=<t0.date and t0.date<=t5.end) but actually I would like to run it with a macro, so I can use it in the future also for different purposes. Thanks a lot, SH.

Sir_Highbury · ‎08-02-2016

Thanks for you advises, I cheked the proc rank but in the end I got what I needed using the dear old proc sql and a proc tabulate to finalize it.

Sir_Highbury · ‎07-31-2016

Dear experts, I have data set (sheet input in the attachment) and on the base of two variables, seller id and revenues, I wouldlike to group the data showing: - top 5 sellers; - residual part; - absolute and percentage values; (sheet output in the attachment). I just used the proc sql to sort and then I selected the top 5 using the outobs in the proc sql but to get the full picure I want is taking too many steps. Any elegant and wuick solution to propose? Thanks in advance. BRs, SH

Sir_Highbury · ‎07-11-2016

ok, anyway my issue is not yet fixed and I did not find any case with sheet_interval="table". From my perspective a bunch of exemplary templates would be for me more helpful than merely teoretical descriptions.

Sir_Highbury · ‎07-11-2016

Thanks! since it is about exporting and formats, is there not a library with some templates that can be easily copied, re-adjusted and used (like for latex)?

Sir_Highbury · ‎07-11-2016

Dear Reeza, attached the result. Am I wrong? BRs, SH

Sir_Highbury · ‎07-11-2016

Hi Tim, ok, I was also guessing that if sheet_interval="none" I would not get several sheets and this parameter should be edited. My question is how? For instance i changed the code as it follows: ods excel file="W:\03-OUT_outputfolder\example.xlsx" /* will apply an appearance style */ style=pearl options( /* for multiple procs/sheet sheet_interval="none" */ sheet_interval="table" /* name the sheet tab */ sheet_name="Example" ); /* add some formatted text */ ods escapechar='~'; ods text="~S={font_size=14pt font_weight=bold}~Abc def ghi"; /* tabular output */ proc print data=table1; var _all_; title 'table'; footnote '*footnote'; run; /* add some formatted text */ ods escapechar='~'; ods text="~S={font_size=14pt font_weight=bold}~tttttttttt"; proc print data=table2; var _all_; title 'table'; footnote '*footnote'; run; ods excel close; I get the tables on two separate sheets but: - the second sheet is named Example2, where should I specify a different name? - I added also the text (ods escapechar='~'; ods text="~S={font_size=14pt font_weight=bold}~tttttttttt";) but it is still on the first wook sheet, where can I set the break between the two pages? If I am not wrong I was thinking to a similis logik like writing documents using Latex. Thanks a gain and best regards, SH

Sir_Highbury · ‎07-08-2016

Dear @Cynthia_sas and @Reeza, thanks a lot for your suggestions, I am checking this ods excel functionality but to summarize your answer: - I cannot do what I want using the out=.... because I got a table with structure different from the proc tabulate; - I can use the ods excel but in this case the data I want will be directly exported (the feature looks anyway brilliant, I am exploring it); - If I wanna get the dable that I want in sas, I should reshape it with the dear old proc sql.

Online Status	Offline
Date Last Visited	‎07-20-2017 08:33 AM

concetenate stringr and variable to generate a new variable

Re: connect sql database DB instance ip port

Re: connect sql database DB instance ip port

connect sql database DB instance ip port

Re: infile delimiter variables shifted

Re: infile delimiter variables shifted

Re: infile delimiter variables shifted

infile delimiter variables shifted

Re: show for the same variable some observations as numeric and some a...

show for the same variable as numeric or date

Re: show for the same variable as numeric or date

Re: call variables using a macro

Re: join on multiple variable selecting matched variable only once

Re: join on multiple variable selecting matched variable only once

Re: ODS Table Output for PROC Means

Proc import csv specify a row for headers and a row for the first obse...

how to refer to a variable using let

Re: sas calculate delta between two dataset

Re: sas calculate delta between two dataset

Re: sas calculate delta between two dataset

Re: sas calculate delta between two dataset

Re: sas calculate delta between two dataset

sas calculate delta between two dataset

easy macro: looping on a data set to select and aggregate data in anot...

Re: SAS reporting: group data

SAS reporting: group data

Re: ods excel multiple sheets

Re: ods excel multiple sheets

Re: ods excel multiple sheets

Re: ods excel multiple sheets

Re: Proc tabulate out export table as in the result