About Alpay

Alpay · ‎06-19-2012

&&country&i.csv will resolve to Country1csv for i=1. && will resolve to & and &i will resolve to 1 in the first pass. Macro facility will try to resolve &Country1csv macro variable reference which fails. The single period following macro variable reference will act as a delimiter and won't be part of the resulting text. You will need to insert another '.' (period) after &&country&i to delimit the value of macro variable from the the file extension. Replace '&&country&i.csv' to '&&country&i..csv' I am not sure about the warning 'WARNING: Apparent symbolic reference D not resolved;' I could not see where macro variable D is referenced in the code.

Alpay · ‎06-18-2012

There is some overlapping of periods (GHI 20110401 20120430,GHI 20120101 20120229,GHI 20120301 20120331) and there is one place where 1 gap in coverage exists (DEF 20120401 20120530) Assuming that the patient is covered for a given month if he has coverage on the first day of that month. data x; input PatID $ Start_Date anydtdte. End_Date anydtdte.; datalines; ABC 20110201 20120229 ABC 20120401 20120430 ABC 20120701 20121231 DEF 20120401 20120530 DEF 20120101 20120229 DEF 20120301 20120331 GHI 20110401 20120430 GHI 20120101 20120229 GHI 20120301 20120331 GHI 20120501 20120930 ; run; proc sort data=x; by PatId; run; data want; array m [0:5]; do i=0 to 5; m =0; end; do until(last.PatId); set x; by PatID; if Start_Date <= '30Jun2012'd or End_Date >= '01Jan2012'd then do; Cnt = INTCK('month',Start_Date,End_Date); do i=0 to Cnt; Month = INTCK('month','01Jan2012'd,INTNX('month',Start_Date,i)); if Month >=0 and Month <= 5 then m[Month]=1; end; end; end; do i=0 to 5; if m = 0 then do;Gap_Month = INTNX('month','01Jan2012'd,i);output;end; end; format gap_Month yymmd7.; keep PatId Gap_Month; run;

Alpay · ‎06-18-2012

I think Proc Freq might be trying to utilize memory to fit all the combinations of class variables in it. Have you tried Proc Summary instead? It will provide you the counts not the percentages which can be computed easily once you get the counts. proc summary data=hpv_single_reasons nway missing; class r_HPV_recode_1 r_HPV_recode_2 r_HPV_recode_3 r_HPV_recode_4 r_HPV_recode_5 r_HPV_recode_6 r_HPV_recode_7 r_HPV_recode_8 r_HPV_recode_9 r_HPV_recode_10 r_HPV_recode_11 r_HPV_recode_12 r_HPV_recode_13 r_HPV_recode_14 r_HPV_recode_15 r_HPV_recode_16 r_HPV_recode_17 r_HPV_recode_18 r_HPV_recode_19 r_HPV_recode_20 r_HPV_recode_21 r_HPV_recode_22 r_HPV_recode_23 r_HPV_recode_24 r_HPV_recode_25 r_HPV_recode_26 r_HPV_recode_27; output out=hpv_single_reasons(rename=_freq_=Count drop=_type_); run;

Alpay · ‎06-18-2012

The numbers you are trying to read seem to exceed the number of digits SAS can represent accurately. Please see http://support.sas.com/techsup/technote/ts654.pdf for an explanation of numeric precision in SAS by operating environment.

Alpay · ‎06-18-2012

Hi Mirisage, I realized I mixed up your name in previous reply. Sorry about that.

Alpay · ‎06-18-2012

Hi Minisage, We can capture the last NPNA_Start_Month for a given account and check whether it is equal to 28Feb2010 or not instead of NPNA_Start_Month greater than 28Feb2010. This way, we can capture accounts gone into NPNA status on 28Feb2010 and come out of NPNA status in subsequent months. data b; informat Current_date date9.; Input Bank_number 1-2 Current_date Account_number $14-23 Balance 25-31 product $33-45 Arrears_Band $47-53 ; format Current_date date9.; cards; 10 28FEB2010 1111111111 6999.99 Personal Loan NPNA 10 31MAR2010 1111111111 6999.99 Personal Loan NPNA 10 30APR2010 1111111111 6999.99 Personal Loan NPNA 10 31MAY2010 1111111111 6999.99 Personal Loan NPNA 10 30JUN2010 1111111111 4444.44 Personal Loan Current 10 31JUL2010 1111111111 4444.44 Personal Loan 30 - 60 10 31AUG2010 1111111111 4444.44 Personal Loan 30 - 60 10 30SEP2010 1111111111 4444.44 Personal Loan 60 - 90 10 31OCT2010 1111111111 4444.44 Personal Loan NPNA 10 30NOV2010 1111111111 4444.44 Personal Loan NPNA 10 31DEC2010 1111111111 4444.44 Personal Loan NPNA 10 31JAN2011 1111111111 3333.21 Personal Loan NPNA 10 28FEB2011 1111111111 2222.22 Personal Loan Current 10 31MAR2011 1111111111 2222.22 Personal Loan 1 - 30 10 30APR2011 1111111111 2222.22 Personal Loan 30 - 60 10 31MAY2011 1111111111 2222.22 Personal Loan 1 - 30 10 30JUN2011 1111111111 2222.22 Personal Loan Current 10 31JUL2011 1111111111 2222.22 Personal Loan 30 - 60 10 31AUG2011 1111111111 2222.22 Personal Loan 60 - 90 10 30SEP2011 1111111111 7777.77 Personal Loan NPNA 10 31OCT2011 1111111111 7777.77 Personal Loan NPNA 10 30NOV2011 1111111111 7777.77 Personal Loan NPNA 10 31DEC2011 1111111111 7777.77 Personal Loan NPNA ; run; data temp; if _n_ = 1 then do; declare hash h(ordered:'a'); h.defineKey('Current_Date'); h.defineData('Current_Date','Bank_Number','Account_Number','Balance','Product','Arrears_Band','NPNA_Start_Month'); h.defineDone(); declare hiter hi('h'); end; NPNA_Start_Month = .; do until(last.Account_Number); set b; where Product IN ('Personal OD','Personal Loan','Res. Mortgage') and Bank_number = 10; by Account_Number; if Arrears_Band = 'NPNA' and NPNA_Start_Month = . then NPNA_Start_Month = Current_Date; else if Arrears_Band ne 'NPNA' then NPNA_Start_Month = .; h.replace(); end; Last_NPNA_Start_Month = NPNA_Start_Month; /* Added 6/18/2012 */ rc = hi.first(); do while(rc=0); Current = 0; One_to_30 = 0; Thirty_to_60 = 0; Sixty_to_90 = 0; Ninety_plus = 0; NPNA = .; if NPNA_Start_Month = . or ( NPNA_Start_Month = Current_Date and /* NPNA_Start_Month > '28Feb2010'd */ Last_NPNA_Start_Month ne '28Feb2010'd) then do; select (Arrears_Band); when ('Current') do;Current = Balance;end; when ('1 - 30') do;One_to_30 = Balance;end; when ('30 - 60') do;Thirty_to_60 = Balance;end; when ('60 - 90') do;Sixty_to_90 = Balance;end; when ('90 +') do;Ninety_plus = Balance;end; when ('NPNA') do;NPNA = Balance;end; end; end; output; rc = hi.next(); end; h.clear(); run; proc summary data=temp nway missing; class Current_Date; var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA; output out=want(drop=_:) sum=; run;

Alpay · ‎06-15-2012

It will get you the unique rows for variables specified in select clause. It is similar to 'group by' but no summary statistics computed. select distinct race, subno, sex from have the result of this query: 1 black male 2 black female 3 latin male 4 black male 5 latin female 6 oriental male Using the result of this sub-query, you can get the counts by race.

Alpay · ‎06-15-2012

Can you provide some example data and the way you want to append the data? Example: Have: Want:

Alpay · ‎06-15-2012

You will need to get distinct values of race, subno and sex in a subquery and then sum it up. proc sql; select race, sum(sex='male') as male, sum(sex='female') as female, count(race) as total from (select distinct race, subno, sex from have) group by race; quit;

Alpay · ‎06-15-2012

Do you have a unique index on the data set X? Can you run a proc contens on the data set X and share the log with us? If so and Y data set has observations that match the key variable values in data set X it will reject those observations in Y when appended. data class; set sashelp.class; run; proc sql; create unique index Name on class ; quit; proc contents data=class; run; proc append base=class data=sashelp.class; run; NOTE: Appending SASHELP.CLASS to WORK.CLASS. WARNING: Duplicate values not allowed on index Name for file CLASS, 19 observations rejected. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: 0 observations added. NOTE: The data set WORK.CLASS has 19 observations and 5 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

Alpay · ‎06-15-2012

What you are doing in above code can be programmed in a data step. Put character arguments of INTNX function in quotes and reference variables by their name in data step instead of macro variable names. e.g: Intnx(month, &dvar, 1, e) vs Intnx('month', dvar, 1, 'e') Get your macro variable's (Stichdatum) value into a variable. Assign values to dvar and hvar variables. Store your format name 'faellig' in variable called fmtname and retain it in your control data set. For low and high end of intervals, use 'l' and 'h' values for hlo variable, respectively. Store your left end and right end values of intervals in Start and End variables (values separated by a dash on the left side of equal sign in proc format statement; e.g.: 20089 - 20453 = "2015"). Store your label in Label variable (e.g.: "2015"). Issue output statements after each interval constructed. Construct do until loops to generate additional rows. You can read in this data set by using cntlin option in proc format. %macro genInt(Stichdatum); data datefmt; if _n_ = 1 then do; Stichdatum = INPUT(SYMGET('Stichdatum'),anydtdte.); dvar = stichdatum; hvar = Intnx('month', dvar, 1, 'e'); end; retain fmtname 'dtfmt'; If intnx('month',dvar,0,'e') < intnx('qtr',dvar,0,'e') then do; hlo = 'l'; Start = .; End = INTNX('Month',Stichdatum,0,'e'); Label = '0_abgelaufen'; output; end; hlo = ' '; dvar = Intnx('qtr', dvar, 1, 'b'); DO UNTIL (Intnx('Qtr', dvar, 0, 'e') > Intnx('year', hvar, 1, 'e')); Start = Intnx('qtr', dvar, 0, 'b'); End = Intnx('Qtr', dvar, 0, 'e'); Label = PUT(Intnx('qtr', dvar, 0, 'end'), DDMMYYP10.); output; dvar = Intnx('qtr', dvar, 1, 'b'); END; DO UNTIL (Intnx('semiyear', dvar, 0, 'e') > Intnx('year', hvar, 2, 'e')); Start = Intnx('semiyear', dvar, 0, 'b'); End = Intnx('semiyear', dvar, 0, 'e'); Label = PUT(Intnx('semiyear', dvar, 0, 'end'), DDMMYYP10.); output; dvar = Intnx('semiyear', dvar, 1, 'b'); END; DO UNTIL (Intnx('year', dvar, 0, 'e') > Intnx('year', hvar, 6, 'e')); Start = Intnx('year', dvar, 0, 'b'); End = Intnx('year', dvar, 0, 'e'); Label = PUT(Intnx('year', dvar, 0, 'end'), YEAR4.); output; dvar = Intnx('year', dvar, 1, 'b'); END; Start = Intnx('year', dvar, 0, 'b'); End = .; hlo = 'h'; Label = Catx(' ','nach' ,PUT(Intnx('year', dvar, -1, 'e'), YEAR4.)); output; format Start End Date9.; keep fmtname Start End hlo Label; run; proc format cntlin = datefmt; run; %mend; %genInt(31May2012);

Alpay · ‎06-15-2012

Does it have to be SQL code? Using hash object along with a hash iterator in a data step can be an alternative way to address this issue. Read records by Account_Number and check out whether Arrears_Band = 'NPNA' or not. If so and the value of NPNA_Start_Month is missing then set it equal to Current_Date. Else, set NPNA_Start_Month value to missing. Load the record into a hash object. After the last record for given account_number read traverse the records for it using hash iterator object. Inıtıalize your sum varaiblles Current = 0; One_to_30 = 0; Thirty_to_60 = 0; Sixty_to_90 = 0; Ninety_plus = 0; NPNA = .; Update the value of your sum variables based on the value of Arrears_Band and output the record. After all records read clear the hash object and start over for the next account_number. After all records for all account_numbers have been read summarize your temp data set. data b; informat Current_date date9.; Input Bank_number 1-2 Current_date Account_number $14-23 Balance 25- 26 product $28-40 Arrears_Band $42-48 ; format Current_date date9.; cards; 10 28FEB2010 1111111111 45 Personal Loan NPNA 10 31MAR2010 1111111111 45 Personal Loan NPNA 10 30APR2010 1111111111 45 Personal Loan NPNA 10 31MAY2010 1111111111 45 Personal Loan NPNA 10 31MAR2010 2222222222 25 Personal Loan Current 10 30APR2010 2222222222 25 Personal Loan Current 10 31MAY2010 2222222222 25 Personal Loan Current 10 28FEB2010 3333333333 24 Personal Loan Current 10 31MAR2010 3333333333 23 Personal Loan 1 - 30 10 30APR2010 3333333333 23 Personal Loan 30 - 60 10 31MAY2010 3333333333 23 Personal Loan 60 - 90 10 30JUN2010 3333333333 23 Personal Loan 90 + 10 31JUL2010 3333333333 85 Personal Loan NPNA 10 30NOV2010 4444444444 25 Personal Loan Current 10 31DEC2010 4444444444 25 Personal Loan NPNA 10 31JAN2011 4444444444 18 Personal Loan 1 - 30 10 28FEB2011 4444444444 18 Personal Loan NPNA 10 31MAR2011 4444444444 18 Personal Loan NPNA 10 30APR2011 4444444444 18 Personal Loan NPNA 10 31MAY2011 4444444444 85 Personal Loan Current 10 31JAN2011 5555555555 25 Personal Loan Current 10 28FEB2011 5555555555 25 Personal Loan NPNA 10 31MAR2011 5555555555 22 Personal Loan NPNA 10 30APR2011 5555555555 21 Personal Loan NPNA ; run; data temp; if _n_ = 1 then do; declare hash h(ordered:'a'); h.defineKey('Current_Date'); h.defineData('Current_Date','Bank_Number','Account_Number','Balance','Product','Arrears_Band','NPNA_Start_Month'); h.defineDone(); declare hiter hi('h'); end; NPNA_Start_Month = .; do until(last.Account_Number); set b; where Product IN ('Personal OD','Personal Loan','Res. Mortgage') and Bank_number = 10; by Account_Number; if Arrears_Band = 'NPNA' and NPNA_Start_Month = . then NPNA_Start_Month = Current_Date; else if Arrears_Band ne 'NPNA' then NPNA_Start_Month = .; h.replace(); end; rc = hi.first(); do while(rc=0); Current = 0; One_to_30 = 0; Thirty_to_60 = 0; Sixty_to_90 = 0; Ninety_plus = 0; NPNA = .; if NPNA_Start_Month = . or ( NPNA_Start_Month = Current_Date and NPNA_Start_Month > '28Feb2010'd) then do; select (Arrears_Band); when ('Current') do;Current = Balance;end; when ('1 - 30') do;One_to_30 = Balance;end; when ('30 - 60') do;Thirty_to_60 = Balance;end; when ('60 - 90') do;Sixty_to_90 = Balance;end; when ('90 +') do;Ninety_plus = Balance;end; when ('NPNA') do;NPNA = Balance;end; end; end; output; rc = hi.next(); end; h.clear(); run; proc summary data=temp nway missing; class Current_Date; var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA; output out=want(drop=_:) sum=; run;

Alpay · ‎06-14-2012

I am not seeing any errors when the above code was submitted. If I replace 'else' with 'els' and submit I get the WARNING but not the ERROR. What version of SAS and environment are you using?

Alpay · ‎06-14-2012

Use SUM function with your criteria as an inline indicator (0,1) in Proc SQL. proc sql; create table y as select *, sum(DISTCD in ('5523','1213')) as cnt from x group by monthyear; quit;

Alpay · ‎06-14-2012

If it's a report output you want you can utilize proc report. data x; input Country $ Town $ Population; datalines; UK A 10 UK A 20 UK B 20 US C 10 US C 20 US D 5 Greece E 20 Greece K 10 ; run; proc format; value newpctfmt . = ' ' other = [percent9.2]; run; proc report data=x nowd missing out=y; column Country Town Population Pct; define Country / group order=data ; define Town / group ; define Population / analysis; define Pct / computed format=newpctfmt.; compute before Country; town_total = Population.Sum; endcomp; Compute Country; if Country ne '' then hold_Country = Country; else Country = hold_Country; endcomp; compute after Country; Country = 'Total'; Pct = .; endcomp; break after Country / summarize style=[font_style=Roman]; compute Pct; Pct = Population.Sum / Town_Total; endcomp; run; If needed, one can get this into a table from proc report's output data set y. data z; set y(drop=_BREAK_); by Country notsorted; if not first.Country or Country = 'Total'; format Pct newpctfmt.; run;

Online Status	Offline
Date Last Visited	‎07-30-2018 04:57 PM

Re: SAS Model Implementation Platform 3.1 New Features

Re: Dates = numbers? HOW

Re: Need help in introducing parallel processing.

Re: How to get one subset like this?

Re: How to get one subset like this?

Re: Top 10 retailors according to Script Count for Tpye

Re: Need help in introducing parallel processing.

Re: Need help in introducing parallel processing.

Re: Why this do until loop using all the observations?

Re: Transform dates to weeks and match with other dates?

Re: Row/column headings colors in tabulate

Re: how avoiding proc sorting before proc transpose

Re: Whats the problem about this loop?

Re: %SYSFUNC format problem

Re: proc sql summary table considering distints subjids

Re: problem of using macro to import multiple csv files

Re: Regarding Dates

Re: Proc Freq - Error: Table too large to process

Re: How can i read numeric obs what is format can i use

Re: Longitudinal data question using proc SQL

Re: Longitudinal data question using proc SQL

Re: proc sql summary table considering distints subjids

Re: How to append multiple columns into a column (with many rows) ?

Re: proc sql summary table considering distints subjids

Re: Copy rows in 1 table to another table

Re: %SYSFUNC format problem

Re: Longitudinal data question using proc SQL

Re: Whats the problem about this loop?

Re: Simple Count

Re: Whats the easiest way to calculate percentages within a group