About RobertNYC

Patrick · ‎10-06-2012

You don't show us your code so it's a bit hard to give you a concrete answer. On a general level: Wouldn't a substring() function with the filename as source string give you what you need? ... substrn(scan("B06 FY13 Report.xml",-2,'/\.'),1,3)

Ksharp · ‎09-12-2012

OK . That would be more simple . proc format; value $ type 'GP'='gp.' 'FTR'='ftr.'; value gp 0-.12='100%' .13-.17='80%' .18-.24='60%' .25-.41='40%' .41<-high='0%' ; value ftr 0-.11='100%' .12-.14='80%' .15-.22='60%' .23-.34='40%' .4<-high='0%' ; run; data want; set have; length LOS_Score $ 4; LOS_Score=putn(percent_18,put(type_new,$type.)); run; Ksharp

Astounding · ‎08-31-2012

One additional issue to think about: what happens if two variable names get shortened, and become identical as a result? The results vary, depending on the methods you choose.

mkeintz · ‎08-27-2012

Very interesting, but you still need another step to have one wide row per agency which I think is what the OP was looking for. But I think that the more important concern about the technique of writing to _FILE_ as a temporary storage location is that you need to be certain that no precision is lost in the PUT statement (but of course you could use the RB8.0 format to preserve precision) In this example of course there's no problem of that sort. Also the OP mentioned 100's of vars, which I presume means either a much longer INPUT statement, or the creation of a macrovar to use in the INPUT statement. (Struck out this sentence - it is wrong - MK).

art297 · ‎08-03-2012

@Robert: I used Total_Homes and tot_points as placeholders so that the total values of each could be accumulated during the loop. Both are dropped by the time the file is actually written. To understand what is actually happening through the two loops, it is easiest (I think) to add a couple of putlog _all_; statements in the code. e.g., data have; informat office $8.; informat certification $20.; informat closed compliance_rate percent3.; input Office Certification & Total_of_Homes Closed Compliance_Rate; cards; Office1 12-month 173 1% 99% Office1 kinship 90 days 23 13% 87% ; data want (drop=Total_Homes tot_points); do until(last.office); set have; by office; if first.office then Total_Homes=Total_of_Homes; else Total_Homes+Total_of_Homes; putlog _all_; end; do until(last.office); set have; by office; Home_Type_Proportion=Total_of_Homes/Total_Homes; Points_Available=Home_Type_Proportion*10; Home_Points_Earned=Points_Available*Compliance_Rate; if first.office then tot_points=Home_Points_Earned; else do; tot_points+Home_Points_Earned; Total_Points=tot_points; Total_Score=Total_Points*10; end; output; putlog _all_; end; run;

Reeza · ‎07-31-2012

You can do it without hash as well, however I did it incorrectly and it is a sort and two data steps. data ec3; set ec2; by descending case_events_id; if _n_=1 then group=1; else if first.case_events_id then group+1; run; data last_close; set ec3; where group=1; run;

Dorota_Jarosz · ‎07-21-2012

Robert, Dates in SAS are numeric variables indicating number of days since January 1, 1960. Once you enter the date correctly as an integer, you can assign various formats. The values are still the same, but the display changes. Format date9. is DDMMMYYYY, e.g. the zero day is 01JAN1960. So, if you assign the value of zero, your date will be January 1, 1960 and it is equivalent to assigning the date value equal to '01JAN1960'd. Your DATE column is displayed with slashes, but the day and month fields do not have leading zeroes, so the slash position is not fixed. This is not date9. format. The length of the string can be between 8 and 10. You can still read it with a date informat though. data have; format date_fst date9.; input date_fst: anydtdte10.; cards; 3/11/2011 . 12/7/2011 8/7/2011 12/8/2011 . 6/7/2012 ; run; options nocenter nodate pageno=1; proc print;run; /* The output will be in date9. format by default, Obs date_fst 1 11MAR2011 2 . 3 07DEC2011 4 07AUG2011 5 08DEC2011 6 . 7 07JUN2012 */ but you can change the format in each procedure. proc print; format date_fst mmddyy10.; run; /* Obs date_fst 1 03/11/2011 2 . 3 12/07/2011 4 08/07/2011 5 12/08/2011 6 . 7 06/07/2012 */ You can change the missing values by assigning zero value to a date variable. This is the same as assigning the '01JAN1960'd value. data want; set have; if date_fst=. then date_fst=0; run; proc print;run; /* Obs date_fst 1 11MAR2011 2 01JAN1960 3 07DEC2011 4 07AUG2011 5 08DEC2011 6 01JAN1960 7 07JUN2012 */ I hope this helps. Let us know if you have further questions. Dorota

Cynthia_sas · ‎07-16-2012

Oh, good eyes, Tom! I didn't notice the double quotes on the %LET statement. Good catch! cynthia

Cynthia_sas · ‎05-30-2012

Hi: Well that example and what's in DOC='HELP' is the only thing I've ever used. But then, I don't generally put formulas into my data or into my output. There may be some papers about using formulas with TAGSETS.EXCELXP, but the main thing in my understanding is knowing what the RC notation for the formula is. Well that and knowing WHAT the correct formula is. And both of those 2 pieces of information come from Excel. You seem to already have those...but you don't have the RC notation for your formulas. And the only way to find out the RC notation is to turn it on in Excel, make a "fake" couple of rows that have the same order of items that you will have in PROC REPORT or PROC PRINT and then copy the RC formula from Excel into SAS. Sort of a pain, I know, but I don't "think" in RC notation, so I always have to reverse engineer the formula. If I were using PROC REPORT, I probably would NOT bother to use a formula and would do my calculations in a COMPUTE block. If you just want to do something like calculate the difference between height and weight (silly calc) for every row in SASHELP.CLASS, then you could do something like this: proc report data=sashelp.class nowd; column sex name age height weight diff1; define sex / order; define name /order; define age / sum; define height / sum; define weight / sum; define diff1 / computed; compute diff1; ** use proc report to compute difference; if weight.sum gt 0 then diff1 = weight.sum - height.sum; else if weight.sum = 0 or weight.sum = . then diff1=0; endcomp; run; The only reason that I can think of for using formulas with TAGATTR is if you are giving your users a report where you want them to change values and you want to use the formula to change the value in the Excel cell when they type a change. But, I rarely allow folks to change the values of the data that comes from SAS -- in general it violates the integrity of my report. If I send a report out to 10 people (as an ODS output file from TAGSETS.EXCELXP) and 9 of the folks don't change the data and 1 of them does change the data in Excel, then there are 2 versions of my report out in the world and if (usually not if, but when) somebody wants to know why person X has a different report then they do, I have to go back and prove that the original report had different numbers. So if you really want a TAGATTR solution, you will have to follow the examples in the above link from Tech Support (and use RC notation). cynthia

PGStats · ‎03-27-2012

The only way to do this kind of merge with SQL is to add some observation IDs. Then it becomes simple : data have1; input (Agency_Name Program_Name) (:&$12.) var1 var2 var3 ; if Program_Name ne lag(Program_Name) or Agency_Name ne lag(Agency_Name) then id = 0; id + 1; cards; Agency A Program 1 1 1 2 Agency A Program 1 1 1 1 Agency A Program 1 1 1 2 Agency A Program 1 1 1 1 Agency A Program 1 1 1 2 Agency A Program 1 1 1 2 Agency A Program 2 1 1 1 Agency A Program 2 1 1 2 Agency A Program 2 1 1 2 Agency B Program 2 1 1 2 Agency B Program 2 1 1 1 Agency B Program 3 1 1 1 Agency B Program 3 1 1 2 Agency B Program 3 1 1 2 Agency B Program 3 1 1 2 Agency B Program 3 1 1 2 Agency C Program 3 1 1 2 Agency C Program 3 1 1 1 Agency C Program 3 1 1 2 Agency C Program 3 1 1 2 Agency C Program 3 1 1 2 Agency C Program 3 1 1 2 Agency C Program 3 1 1 1 Agency D Program 1 1 1 2 Agency D Program 1 1 1 2 Agency D Program 1 1 1 2 Agency D Program 1 1 1 2 Agency D Program 1 1 1 2 Agency D Program 2 1 1 2 Agency D Program 2 1 1 2 Agency D Program 2 1 1 2 Agency D Program 2 1 1 2 Agency D Program 2 1 1 2 ; data have2; input (Agency_Name Program_Name) (:&$12.) Q1 Q2 Q3 ; if Program_Name ne lag(Program_Name) or Agency_Name ne lag(Agency_Name) then id = 0; id + 1; cards; Agency A Program 1 1 1 2 Agency A Program 1 1 1 1 Agency A Program 2 1 1 2 Agency A Program 2 1 1 1 Agency A Program 2 1 1 2 Agency B Program 2 1 1 2 Agency B Program 2 1 1 1 Agency B Program 3 1 1 2 Agency B Program 3 1 1 2 Agency C Program 3 1 1 2 Agency C Program 3 1 1 1 Agency C Program 3 1 1 1 Agency C Program 3 1 1 2 Agency D Program 1 1 1 2 Agency D Program 2 1 1 2 Agency D Program 2 1 1 2 ; proc sql; create table want as select h1.Agency_Name, h1.Program_Name, h1.var1, h1.var2, h1.var3, h2.Agency_Name as Agency_Name2, h2.Program_Name as Program_Name2, h2.q1, h2.q2, h2.q3 from have1 as h1 natural left join have2 as h2; PG

MikeZdeb · ‎03-09-2012

hi ... another idea data x; retain ftc1-ftc100 1 a b c 'other'; run; proc sql noprint; select catt(name, '= "Q' , compress(name,,'kd'),'"') into :labels separated by ' ' from dictionary.columns where libname eq 'WORK' and memname eq 'X' and name eqt 'ftc'; quit; proc datasets lib=work nolist; modify x; label &labels; quit; %symdel labels;

Cynthia_sas · ‎02-21-2012

Hi: I agree with Art -- the structure of the data is going to make a difference when you are trying to create survey tables. If you need page numbers, then you don't want to go down the HTML road. You should probably stick with PDF or RTF destinations. I don't have a lot to add. I do have a few observations, though. Almost all my questions from students who are doing surveys are about using PROC TABULATE or PROC REPORT for the survey tables instead of using PROC FREQ. There's a TABULATE example in the doc about creating multi-response survey tables. When your data are structured so that you have 1 obs with Q1, Q2, Q3 individual variables, it leads to needing multiple TABLE statements or multiple procedure steps. But if your data were in a slightly different structure, then TABULATE could do it with BY and/or PAGE processing and REPORT could do it with BY or PAGE processing -- no TABLE template needed. TABULATE and REPORT both have ways to directly provide a format (without a TABLE template) and they both have ways to provide a "header" on top of each table (TABULATE with PAGE dimension, REPORT with COMPUTE BEFORE). Even FREQ could take data in a different structure and produce multiple tables if you just used BY group processing (such as BY QUESTION). And, for changing fonts, both TABULATE and REPORT have the STYLE= overrides. If you can get what you want out of FREQ and you only have 3 questions, then that's probably an acceptable approach, but TABLE templates and STYLE templates will be an extra learning curve. But if you have something closer to 50-100 questions, then I'd recommend taking a look at some of this information and the examples. cynthia These papers discuss the production of survey "books" or survey tables: http://support.sas.com/resources/papers/proceedings09/150-2009.pdf http://analytics.ncsu.edu/sesug/2010/CC02.Go.pdf http://www.nesug.org/proceedings/nesug05/ap/ap18.pdf http://www2.sas.com/proceedings/sugi31/172-31.pdf http://www2.sas.com/proceedings/sugi30/091-30.pdf http://www2.sas.com/proceedings/sugi31/194-31.pdf http://www2.sas.com/proceedings/sugi23/Posters/p184.pdf And, the TABULATE doc even has an example of producing output for multi-response survey data: http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#p1mcul17bwwvfhn19hoojfbxp2oy.htm and a Tech Support note on restructuring your data: http://support.sas.com/techsup/technote/ts277.html

Haikuo · ‎02-12-2012

That is odd. I can't repeat your error. Please provide your error part of your log. Here is what I have just tried: data have; input POINTS; cards; 1.25 .77 .99 1.13 .03 1.03 .89 1.33 ; data want; set have; points=ifn(points>1.05,1.05,points); run; proc print;run; And this was my log: 587 data have; 588 589 input POINTS; 590 591 cards; NOTE: SAS went to a new line when INPUT statement reached past the e NOTE: The data set WORK.HAVE has 8 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 601 ; 602 603 data want; 604 605 set have; 606 607 points=ifn(points>1.05,1.05,points); 608 609 run; NOTE: There were 8 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WANT has 8 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.12 seconds cpu time 0.01 seconds 610 611 proc print;run; NOTE: There were 8 observations read from the data set WORK.WANT. NOTE: PROCEDURE PRINT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds And this is the output: Obs POINTS 1 1.05 2 0.77 3 0.99 4 1.05 5 0.03 6 1.03 7 0.89 8 1.05 Everything seems normal to me. Ifn() is a function, if is a statement. In this case, you can do it both ways. However, in the context of my code, it has to be ifn(). You need to rephrase the code to be able to use 'if'. For details, please refer to SAS manual. Haikuo

RobertNYC · ‎02-06-2012

Thanks so much.

Haikuo · ‎02-03-2012

For Q1, SQL plus intnx() mentioned by mojerry will make one of the solutions. data have; infile cards; input ChildID $10. ACT $ ACTDate :mmddyy8. AgencySpellBegin : mmddyy10. AgencySpellEnd : mmddyy10.; format ACTDate date9. AgencySpellBegin date9. AgencySpellEnd date9.; cards; Childid1 M999 11/30/11 9/12/01 12/12/2414 Childid1 M956 11/12/11 9/12/01 12/12/2414 Childid2 M990 7/12/11 7/12/02 2/12/2414 Childid3 M999 1/1/12 6/13/04 2/12/2414 Childid3 M900 11/11/11 6/13/04 2/12/2414 Childid3 M956 10/12/11 6/13/04 2/12/2414 Childid4 M956 1/1/11 8/9/04 1/12/12 Childid5 M450 9/12/11 7/12/05 2/12/2414 Childid6 M956 8/12/11 7/12/03 1/12/11 Childid7 M956 1/12/12 1/12/09 2/12/2414 ; /*Q1 , awols and returns*/ proc sql; create table awol as select distinct childid from have where act='M956' and actdate>=intnx('month',today(),-7,'sameday') ; create table return as select distinct childid from have where actdate>=intnx('month',today(),-7,'sameday') group by childid having sum(act='M956')>0 and sum(act='M999')>0 ; quit; Please note, if you want to define your month from the first day of every month, you need to remove 'sameday' option from intnx(). Q2 seems to be straighforward question, however, results could vary depend on what you want. Please refer a recent discussion on SAS-L. http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1201B&L=sas-l&P=R13704 Regards, Haikuo

Online Status	Offline
Date Last Visited	‎05-13-2019 07:07 PM

Re: Using Proc SQL to Calculate the Average Time between Purchases for...

Re: Using Proc SQL to Calculate the Average Time between Purchases for...

Using Proc SQL to Calculate the Average Time between Purchases for a C...

Re: iterative data reduction macro

Re: iterative data reduction macro

Re: iterative data reduction macro

iterative data reduction macro

Proc REG: Is there an option for log transformation of variables?

Re: Looking for a function which counts the number of numeric digits i...

Looking for a function which counts the number of numeric digits in a ...

Re: Using Proc SQL to Calculate the Average Time between Purchases for...

Re: iterative data reduction macro

Re: Aggregation and Group by processing

Re: Aggregation and Group by processing

Re: Proc Logistic: Rebuild models when errors in build data are disco...

A/B Test: determine if a lift measured sales is significant

Re: Defining a Password by Characters in File Name

Re: scoring based on a range of values

Re: Shorting variable length

Re: Combining two tables with same variables and % difference

Re: tricky numeric transformations

Re: By Group Processing

Re: Modifying Variables for date values

Re: Macro Variable Issue and PDF

Re: Embedding Excel Formals

Re: A Difficult Join

Re: Assigning labels to a large amount of variables

Formatting Crosstabs

Proc SQL: When Then Else

Formatted Frequency Tables for Reports

Re: Queries that have to do with dates