About Sir_Highbury

Sir_Highbury · ‎07-08-2016

Dear experts, I saw the following link: http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets-from-sas/ and I am trying to getting familiar with the ods excel that looks interesting (despite my sas version does not allow me to use the proc export with the format .xlsx). Where should I change this code to get the sme data also in another worksheet? ods excel file="W:\03-OUT_outputfolder\example.xlsx" /* will apply an appearance style */ style=pearl options( /* for multiple procs/sheet */ sheet_interval="none" /* 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=perm.Summary_table_xxxxxxxx; var _all_; title 'table'; footnote '*footnote'; run; ods excel close; Thanks, SH.

Sir_Highbury · ‎07-08-2016

Never heard before, I am googling it despite that my initial wish was to have the table with the structure I want directly in SAS. http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets-from-sas/ Anyway you are saying that there is no direct way (using out or similar) and to do it I have to write 4 additional lines of code or? In case yes, then I reshape the data as I want using the loyal proc sql introducing two new variables. It a pity that a so easy task can get so complicated.

Sir_Highbury · ‎07-08-2016

Dear @Reeza, is there then another way to export the table into excel?

Sir_Highbury · ‎07-08-2016

Dear experts, running the following proc tabulate I get the attached output. proc tabulate data=Check01; class data_source YYYY; var tot_amount; table data_source, tot_amount*YYYY; ;run; how can I specify: proc tabulate data=Check01 out=....; in order to get the same table as in the attachment? Thanks, SH

Sir_Highbury · ‎07-07-2016

well let's assume that the input is the following one with revenue from revenue1 to revenue100. data have; source="y_2016"; segment="a"; revenue1=100; revenue2=70; output; source="y_2016"; segment="b"; revenue1=50; revenue2=60; output; source="y_2015"; segment="a"; revenue1=90; revenue2=70; output; source="y_2015"; segment="b"; revenue1=30; revenue2=700; output; run; The output should be done in the same way, repeating the calculation for each variable. Basically I am trying to calculate difference between two tables from different years (2015 and 2016) aggregated with the same granularity (all the variables by segment).

Sir_Highbury · ‎07-07-2016

Dear @Astounding, thanks a lot, it fits to the case I provided my my case is a bit more complicated: I do not have only revenues but several variables and I cannot list the calculation for all of them. Anny way to automatize it? Thanks again, SH.

Sir_Highbury · ‎07-07-2016

Dear experts, here what I have: data have; source="y_2016"; segment="a"; revenue=100; output; source="y_2016"; segment="b"; revenue=50; output; source="y_2015"; segment="a"; revenue=90; output; source="y_2015"; segment="b"; revenue=30; output; run; and what I want: data want; source="y_2016"; segment="a"; revenue=100; output; source="y_2016"; segment="b"; revenue=50; output; source="y_2015"; segment="a"; revenue=90; output; source="y_2015"; segment="b"; revenue=30; output; source="delta"; segment="a"; revenue=10; output; source="delta"; segment="b"; revenue=20; output; source="delta%"; segment="a"; revenue=10/90; output; source="delta%"; segment="b"; revenue=20/30; output; run; how can I specify the calculation I want?

Sir_Highbury · ‎07-06-2016

Dear experts, let's assume the following variables: shop_ID, bolean_variable, revenue201601, revenue201602, revenue201603, revenue201603 for a total of 10 observations. how can set a proc sql as it follows: proc sql; create table abc as select shop_ID, bolean_variable, sum (revenue:) from xyz group by shop_ID, bolean_variable ;quit; How can I specify to do it for some variables without listing them one by one? Thanki in advance and BRs, SH

Sir_Highbury · ‎07-06-2016

Hi Kurt, thanks for your reply, nice to know the motivations active here, I also think that helping the other without direct interests is a good training. But I thought that here, since SAS is not open source, the people are active in order to be paid. So are you working in a local data processing division of a large international insurance company? Which is your focus? ##- Please type your reply above this line. Simple formatting, no attachments. -##

Sir_Highbury · ‎07-04-2016

Dear experts, assuming the following data set "have": data have0; country="BU"; output; country="JP"; output; country="US"; output; run; data have1; value_list="US_ UK _JP NW"; output; value_list="US_ UK _JP NW"; output; value_list="US_ UK _JP NW"; output; run; data have; merge have0 have1 ;run; I would like to obtain the following one: data want country="BU"; output; run; which is the most short, understandable and elegant way?

Sir_Highbury · ‎07-04-2016

ok, fair enough. I will keep trying and in case I will post something new.

Sir_Highbury · ‎07-04-2016

1. I have a macro which is based on the variable &var; 2. A call execute is used; If for istance the &var assume the variable "segment", I did not get as output the values assumed by the variable "segment" but only the name of the variable "segment" itself. The example works perfectly, but when I switched to the macro I did something wrong.

Sir_Highbury · ‎07-04-2016

assuming you would do the same within a macro: data want (drop=country rename=(tmp=country)); length tmp $2000; set have end=last; retain tmp; tmp=catx(",",tmp,country); if last then output; run; I changed the code as it follows: data want (drop=&var. rename=(tmp=&var.)); length tmp $2000; set have end=last; retain tmp; tmp=catx(",",tmp,&var.); if last then output; run; do you already notice a mistake? Instead of getting the value list I get the variable name....

Sir_Highbury · ‎07-04-2016

it was just a question, I did not have any particular expectations. You could be a philanthropy or a sas consultant looking for clients or whatever. Nothing against any motivation, ##- Please type your reply above this line. Simple formatting, no attachments. -##

Sir_Highbury · ‎07-04-2016

if SAS is not your company but you are working for a different one, just for the sake of curiositiy, why do you participate to this forum answering? I would expect it only on "free projects" like linux forum or stack overflow.

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...

ods excel multiple sheets

Re: Proc tabulate out export table as in the result

Re: Proc tabulate out export table as in the result

Proc tabulate out export table as in the result

Re: compaare observations and extract deltas

Re: compaare observations and extract deltas

compaare observations and extract deltas

proc sql sum several variables and group by

Re: concatenate observation using proc sql

select all the values of a variable that are not contained in the valu...

Re: concatenate observation using proc sql

Re: concatenate observation using proc sql

Re: concatenate observation using proc sql

Re: concatenate observation using proc sql

Re: concatenate observation using proc sql