About haydn89

haydn89 · ‎09-04-2017

Hi PGStats, You've identified a limitation of my motivating example, rather than a mistake about what the data actually represents. I apologies if this has caused confusion. In the motivating example, I said that each observation represented ids that shared a common variable, such as income. If this was strictly true, then you are right, actual_c doesn’t say that A=H. If it helps, I’ll be more abstract. The actual_data looks like this: data 'actual_data' obs id1 id2 1 A B 2 B H The interpretation of this data is that A=B and B=H. By the properties of transitivity, A=H. The final output I want is: data 'final_output' id clust A 1 B 1 H 1 Thanks for all your help!

haydn89 · ‎09-04-2017

Hi PGStats, Thanks for the suggestions! Unfortunately, my workplace does not have SAS/OR. In addition, I can’t follow through with your second ‘simpler’ solution because the data isn’t quite as neatly setup as your example data set ‘c’. In your data set ‘c’, observation 1 looks like this: data ‘pgstats_c’ obs id1 id2 id3 1 A B H Even though it is true that A=B=H, the data actually looks like this: data ‘actual_c’ obs id1 id2 id3 1 A B 2 B H In this context, the ‘simpler’ solution does not work, because ‘H’ is not identified as being part of the same group as ‘A’ and ‘B’. I noticed that you published a macro back in 2015: https://communities.sas.com/t5/General-SAS-Programming/Finding-all-connected-components-in-a-graph/m-p/230156 This works! Although I confess that I am using it without fully understanding it. Could you prescribe an introductory text on ‘network theory’? Thanks for all your help!

haydn89 · ‎09-02-2017

Thanks PGStats! What a great little program. I have never used the ‘cmiss’ function before or the ‘sortc’ call routine. I also love the loop to find all unique pairs of identifiers. I certainly got more than I bargained for! Can you help me extend this a little more? The proc sql creates the table ‘same’ which looks like this: data ‘same’ Obs same 1 a-b 2 c-d imagine instead that the output looked like this data ‘same_more_likely’ obs same 1 a-b 2 a-h 3 c-d 4 b-h In this scenario, a=b=h. There are situations where there are more than two ids related to the same person, so this output is more likely. I want to manipulate the output to look like this: data ‘new_same’ id group a 1 b 1 h 1 c 2 d 2 Any thoughts?

haydn89 · ‎09-01-2017

I want to compare sets (that is the mathematical notion of a set and not the SAS statement) in two different data sets to determine which identifiers are not unique. For example, consider the two data sets below: data ‘common’ and data ‘address’. Data common groups identifiers that have some arbitrary variables in common (for example, they might have exactly the same income with a very high level of precision). Data ‘common’ looks like the table below, and you would interpret that id ‘A’ and id ‘B’ share the same variable (e.g. income) in common. Data ‘common’ Obs id1 id2 id3 1 A B 2 C D 3 E F G In the second data set (data ‘address’) I have address information. I can use this data set to determine which identifiers share the same address. For example, in the table below, id ‘B’ and id ‘A’ have the same address ‘Fake 1’. Data ‘address’ Address id1 id2 id3 Fake 1 B A Fake 2 C D E Fake 3 Y K Z In this specific example, id ‘A’ and ‘B’ are actually the same person. One of the observations needs to be dropped because they are not unique. How would you use SAS to address this problem? One of the challenges I face includes the order of the set. For example, even though id ‘A’ and id ‘B’ are in the same row of data, they are clearly arranged differently in data ‘common’ and data ‘address’. In addition, id ‘C’ and id ‘D’ (which actually represents the same person) have an additional person (id ‘E’) at the address ‘Fake 2’. Any help would be greatly appreciated! I'm using SAS 9.4 enterprise guide.

haydn89 · ‎11-25-2014

Ahhh yes! Replace the summary procedure with a data step that similarly summarises the data. Same amount of coding with much better output. That definitely is better! Thanks Reeza!

haydn89 · ‎11-25-2014

Hello all, Wondering if anyone has a more elegant solution to achieve the following tabular output (see pdf attached entitled Goal.pdf). Ignore the values! They are just there as an example. We have a data set that looks like this (see listing below): Obs gender country _TYPE_ _FREQ_ var1 var2 var3 1 . 1 1 2 200 2000 20000 2 . 2 1 2 200 2000 20000 3 1 . 2 2 200 2000 20000 4 2 . 2 2 200 2000 20000 It was created using the following sas program: data g; do gender=1 to 2; do country=1 to 2; var1=100; var2=1000; var3=10000; output; end; end; proc summary data=g; types gender country; class gender country; var var1 var2 var3; output out=t2 sum=; proc print; However, the following tabulate procedure results in missing values in the column headers: proc tabulate data=t2 missing; var var1 var2 var3; class gender country; table (var1 var2 var3) , gender country / row=float ; keylabel sum=' '; One solution is to reorganise the data like this: data t2; set t2; if gender = 1 then level = 1; else if gender = 2 then level = 2; else if country = 1 then level = 3; else if country = 2 then level = 4; if level in(1 2) then title = 1; else if level in(3 4) then title = 2; run; proc print data = t2; proc format; value title_fmt 1='Gender' 2='Country'; value level_fmt 1='Male' 2='Female' 3='Country 1' 4='Country 2'; run; However, I was wondering if there was an alternative approach? One that is simpler? The output doesn't have to be created from proc tabulate. Any advice would be greatly appreciated! Haydn.

haydn89 · ‎10-30-2014

Thank you Ballardw, Using your approach I would have my data steps, and some format procedures, laid inside my call to the ods destination. I guess I could reorient my sas jobs to get around this. Cheers

haydn89 · ‎10-23-2014

Thank you Reeza, That is a great approach! I create multiple sas jobs and files to make my code more manageable (i.e. read and debug).

haydn89 · ‎10-23-2014

Hello everyone, I want to know the best way to create a series of reports with conditional output and title names, preferably in relation to the order in which the program is run. I have a series of SAS programs, and each program uses different input data. I use the excelxp tagset to export a proc tabulate from my SAS programs into an xml file. I then use excel VBA to merge all the xml files into one workbook. One problem that often arises is when I am asked to re-arrange the tables in my report. I try to make sure the SAS programs reflect the order of the output and title names in my report for reference later on. For example, let’s say that I have three programs, called table1.sas, table2.sas and table3.sas. I specify the file name in my output statement as table1.xml, table2.xml and table3.xml, and the title names in my tabulate procedures are called Table 1, Table2 and Table 3 respectively. Sometimes, after creating and running the programs, I am asked to make table3.sas into table1.sas. Consequently, I have to go back and re-name ALL the SAS programs (i.e. table3.sas becomes table1.sas, table1.sas becomes table2.sas and table2.sas becomes table3.sas), and similarly, rename the filenames and title names. This can become very time consuming, especially when I have over 50 tables. My current thought is that I might find some use from ODS Document (which I have just discovered), but if someone has a better idea, then I am all ears. My workplace is currently running SAS 9.1.3 on a UNIX platform. We are currently in the process of upgrading to SAS Enterprise Guide 6. Any assistance would be greatly appreciated. Kind regards, Haydn

haydn89 · ‎05-08-2014

Hi all, Looking for some help. I am trying to use the inline formatting operator escapechar to change the font size of a footnote in my table. I can’t get it to work properly. Other style attributes work, such as font_weight, but font_size does not. I should note that I am trying to output this table to an xml file using ods tagsets.excelxp. I am running SAS using solaris on 9.1.3 service pack 4. I am using a custom style to render my table but I tested the inline formatting without the custom style and it still didn’t work. I try and reformat the size using the inline formatting operator 3 times: 1. 'r1'="Rate of return ^S={font_weight=bold font_size=3pt}a" 2. 2012="2011^{unicode '2013'}12^S={font_weight=bold font_size=8pt font_face='calibri'}p^S={}" 2013="2012^{unicode '2013'}13^S={font_weight=bold font_size=8pt font_face='calibri'}y^S={}" 3. title1 "Appendix table ^S={font_size=6pt}d"; I’ve attached the SAS code (cara.sas), log (cara.log) and the code for my custom style as well (chp2_1.sas). I've also attached the data set output that I am working with (aus.sas7bdat and sta.sas7bdat). If anyone has an idea your guidance would be much appreciated. Kind regards, Haydn

haydn89 · ‎03-23-2014

Thanks heaps Cynthia!

haydn89 · ‎03-21-2014

Thanks ballardw. I didn't think about approaching the problem that way.

haydn89 · ‎03-20-2014

Hello all, I was hoping someone could help me to better format my proc tabulate output to look like the final output that I am trying to achieve. Two questions come to mind: 1. sI * Is it possible to conditionally assign variables to certain class levels? 2. * How can I use proc format or proc tabulate to conditionally format my cell values? To emphasise what I mean, take a look at the output that I am trying to achieve (table1_1.xls) compared to the output that is automatically generated (table1_1.xml) from the sas code (table1_1.sas). The data set used to create the tables is also attached (all.sas7bdat). For each of the three years there is a column representing the estimated average for the population, and a relative standard error (table1_1.xml). Our workplace style guide dictates that the only year that relative standard errors (rse) values are output is for the ‘year of interest,’ which is 2011-12 in this instance. Is it possible to manipulate my code so that only rse values are output for 2011-12 (i.e. like table1_1.xls) rather than each year (i.e. table1_1.xml)? At the moment, I am using excel to format the results after I have exported the data in an xml format using tagsets.excelxp. All of my financial results have to formatted as [ # ### ##0;-# ### ##0 ] (table1_1.xls). Financial results (i.e. table rows with units labelled ‘$’) in 2010-11 need to be rounded to the nearest 10, 2011-12 financial results need to be rounded to the nearest 100 and 2012-13 results need to be rounded to the nearest 1000. My results for “rate of return” and “interest to receipts ratio” have to be rounded to the first decimal place, while all other percentage variables are rounded to the nearest whole number. Finally, my rse values also need rounded to the nearest whole number and have brackets. Is there a way to apply these formats using proc tabulate or proc format? Formatting the results using SAS rather than manually in excel would save me a lot of time. Any help is appreciated

haydn89 · ‎02-19-2014

Thank you very much Cynthia, your suggestion worked a treat! It will certainly suffice in the short term. Kind regards, Haydn.

haydn89 · ‎02-19-2014

Hello all, I'm looking for some help with manipulating the msoffice2k_x tagset. Firstly some background. I am trying to create a template using sas that looks as close as possible to the template style I use at work. I am using the msoffice2k_x tagset because it exports the results into excel, and I can import and save my workplace logo in the title. I am led to believe xml files can't do this, ruling out the use of the excelxp tagset. I have been experimenting using some sashelp data sets. Unfortunatley, when I have mulitple titles in my tabulate proceduce, there is a row space between each title. You can see a copy of the code used attached in chp2_1.sas and the actual output in custom.xls. I would like to get rid of the row space between title1 and title2. I am led to believe that there is such an option in the excelxp tagset called skip_space, but I don't know of anything equivalent for the msoffice2k_x tagset. I can't use the excelxp tagset because I want to be able to import and save the departments logo at the top. I read somewhere that you can create options in the msoffice2k_x tagset that mimics the same options as the excelxp tagset. Does anyone know how to do this or if there is a better way to get rid of the row space between title1 and title2? I apologise in advance if I have not provided enough information. I am very new to the SAS communities forum. Kind regards, Haydn.

Online Status	Offline
Date Last Visited	‎01-29-2018 02:35 AM

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Comparing ‘sets’ in two data sets using SAS

Re: Better tabular output

Better tabular output

Re: Conditional ods file and output title names

Re: Conditional ods file and output title names

Conditional ods file and output title names

Inline formatting using ods escapechar

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Re: Conditional ods file and output title names

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Re: Comparing ‘sets’ in two data sets using SAS

Comparing ‘sets’ in two data sets using SAS

Re: Better tabular output

Better tabular output

Re: Conditional ods file and output title names

Re: Conditional ods file and output title names

Conditional ods file and output title names

Inline formatting using ods escapechar

Re: Conditionally formatting data using proc tabulate

Re: Conditionally formatting data using proc tabulate

Conditionally formatting data using proc tabulate

Re: msoffice2k_x skip space option

msoffice2k_x skip space option