About overmar

overmar · ‎01-15-2014

I like to use SQL to take minimums, but other people could easily give you data step commands to do both. But once you have the minimum specified from sql, just tell it to replace the age variable in data step. Hope this helps. proc sql; create table almost as select distinct subjectid, year, age, (min(age)) as f_age from have group by subjectid; quit; data want; set almost; age = f_age; drop f_age; run;

overmar · ‎01-15-2014

Ok so I think I understand what you are trying to do. I think that step 1 is that you are trying to 1.) get a distinct count of how many lenders there are to each package 2.) get a distinct count of how many lenders in a list are in a package. If this is the case code is below: proc sql; create table lend_in_pack as select distinct packageid, (count(lenderid)) as numlend /*This counts the number of lenders are in each package*//*If you wanted lenders to be counted once you would add unique( after count and finish it with a )*/ from table1 group by packageid; create table spec_lend_in_pack as select distinct packageid, (sum(case when lenderid in (50 74 78 /*All of the other lenderids in here*/) then 1 else 0 end)) as spec_lend /*This takes the list of specific lenderids and outputs a 1 or 0 if it is in the list, then sums the list to give you your count, once again this isn't taking distinct lenderids so if one shows up 5 times, it will be counted 5 times, if you wanted specific ones, I would recommend just creating a table that only gives you distinct rows by running a select distinct * from table1 and then running this code again*/ from table1 group by packageid; quit; Hope this helps.

overmar · ‎01-14-2014

Jsendzik, I faced a similar problem to you and created the below code to solve it. I had been using the pipe statement but wanted to have the dir file location directly inserted so I wouldn't have to write it out and this was the code that ended up working. The link to my question is it also includes some other options to do the same thing. Of note if you want to do anything with the file names you likely will need to use a %scan function, but that is for another time. %let source = C:\User /*Insert your drive path*/; %macro read_files (type = ); /*type is the extension of the files you want to keep, ie csv txt*/ filename _dir_ "%bquote(&source.)"; data filenames(keep=memname); handle=dopen( '_dir_' ); if handle > 0 then do; count=dnum(handle); do i=1 to count; memname=dread(handle,i); output filenames; end; end; rc=dclose(handle); run; filename _dir_ clear; data dirlist ; set filenames; if substr(memname,length(memname)-2,3) ~="&type" then delete; file_name = substr(memname,1,length(memname)-4); keep file_name; run; %mend;

overmar · ‎01-13-2014

The easiest way likely would be to use proc sql, though that is just what I prefer to go down columns. As long as you just want the aggregates of each of these groups the below code will work. Granted if you wanted to tack it on to the bottom of the file that the data came in from then you could not rename any of the variables and use union corr to the original dataset. proc sql; create table want as select distinct items, count(unique(dept)) as c_dept, (sum(orders)) as s_orders, (sum(quantity)) as s_quan, (sum(net_amount)) as s_net from have group by items; quit;

overmar · ‎01-13-2014

This paper explains how to do it and the comparisons of the different SAS techniques to identify duplicates and on pg 5/6 identifies how to mimic dupout in proc sql. The thing is that you will not get as clean of an output because you will also get the copy of the non-duplicated observation rather than just the observations which were removed. http://www.nesug.org/proceedings/nesug06/cc/cc24.pdf data x; input a b c; datalines; 1 1 1 1 1 1 1 1 1 1 2 1 1 2 1 2 2 2 1 2 2 ; run; proc sql; create table y as select distinct a, b, c from x; create table counts as select a, b, c, count(*) as IDCount from x group by a, b, c; select IDCount, count(*) as N from counts group by IDCount; create table z as select T.* from x T, counts C where T.a = C.a and t.b=c.b and t.c=c.c and C.IDCount > 1 ; quit;

overmar · ‎01-12-2014

Your best bet is likely to either use GEE (Generalized Estimating Equations) in Proc Genmod SAS/STAT(R) 9.22 User's Guide or here are another couple of ways to use clusters in SAS http://nesug.org/proceedings/nesug06/an/da01.pdf Hope this helps.

overmar · ‎01-12-2014

I don't know if this is really all that more direct than Arthur's solution but it has fewer lines of code... data havea; set have; array mn(24) month1-month24; array nm(24) _TEMPORARY_; array sb(24) _TEMPORARY_; array lg(24) _TEMPORARY_; do i = 1 to 24; nm(i) = 0; if mn(i) = 1 then sb(i) = 1; else sb(i) = 0; if mn(i) = 1 and i = 1 then nm(i) = 1; else if sb(i) = 1 then nm(i) = (sb(i)) + (nm(i-1)); else nm(i) = 0; end; long = max(of nm ); do j = 1 to 24; if nm(j) = long then lg(j) = 1; else lg(j) = 0; times = sum(of lg ); end; run;

overmar · ‎01-12-2014

Namrata, This should do what you want: For reference I nested the creation of the sum variables in the joins in testb, but this could just as easily be done in two separate tables and then joined in, in the same manner. I maxed the values in testa bc you only wanted the best value for duplicates. Hope this helps. proc sql; create table testa as select distinct year, firm, industry, (max(value)) as value, (case when (max(value)) >0 then 'A' else 'N' end) as status from test group by year, firm, industry; create table testb as select distinct a.*, b.acount, c.numfirm from testa a left join (select distinct year, firm, industry, (sum(case when status = 'A' then 1 else 0 end)) as acount from testa group by year, firm, industry) b on (a.year=b.year and a.firm=b.firm and a.industry=b.industry) left join (select distinct year, count(firm) as numfirm, industry from testa group by year, industry) c on (a.year=c.year and a.industry=c.industry); quit;

overmar · ‎01-09-2014

Aha, that makes sense, I would have figured it out if I had run the code, but now I will think about it before running it. Thanks

overmar · ‎01-09-2014

Ok so the issue was that you actually only wanted one row per line and you wanted the minimum of the values which met your requirements. Data step does a much better job of picking off the first observation, but if you wanted to use SQL and combine everything into one statement the below code will work. It takes the distinct IDs and their date from table one and looks at the difference between a.date and b.date and then outputs all of those which are between 1 and 31 days and it then takes the minimum date and only reports that as date2. Since distinct and group by were used and there are no duplicate values for a.date this code will produce 3 IDs, 3 dates, and the first date for all 3 IDs and Dates which met your criteria. proc sql; create table want as select distinct a.id,a.date, (min(case when datdif(a.date,b.date,'act/act') between 1 and 31 then b.date else . end)) format=mmddyy10. as date2 from one as a left join two as b on a.id eq b.id group by a.id; quit;

overmar · ‎01-09-2014

Odd question, I tend to use select distinct on just about everything that I do. Many time regardless if I use a summary function, at this point I think its a force of habit. Other than the possibility of losing duplicate records am I causing any other specific issues for myself?

overmar · ‎01-09-2014

Or you could do it in Proc Sql without having to worry about sorting and it will just take the maximum age of each distinct a contract number and report it as max_age: Proc sql; create table wage as select contract_no, first_name, age, (max(age)) as max_age from dataset group by contract_no; quit; Hope this helps, Robert

overmar · ‎01-08-2014

Sorry I didn't read your code all the way through and it appears that you put ' around the column names, if you do that proc sql thinks that this is a character string rather than a column name, so take those out for the code to run, final code would be: proc sql; create table MyTable as select distinct (header_1) as uniquenumbers from Originaltable group by header_1 having (count(header_1)) = 1; quit;

overmar · ‎01-08-2014

I forgot to suggest an addition of group by to the earlier code and do not put a semi-colon after as, this stops sas the semi-colon should go at the end of the proc sql statement (in this case after having). This is why it was giving you a "select" error, bc you had ended the code before you had given it a select statement. This code should now sufficiently run: proc sql; create table MyTable as select distinct ('header_1') as uniquenumbers from Originaltable group by header_1 having (count('header_1')) = 1; quit; Hope this helps Robert

overmar · ‎01-08-2014

What is the full code that you had written? Because right now this doesn't make much sense.

Online Status	Offline
Date Last Visited	‎08-26-2016 12:00 PM

Re: Weighted v Unweighted data in proc means and proc univariate

Re: weighted percentiles

Re: Weighted v Unweighted data in proc means and proc univariate

Re: weighted percentiles

Weighted v Unweighted data in proc means and proc univariate

Re: weighted percentiles

Re: weighted percentiles

Re: proc sql group by summing several variables

Re: weighted percentiles

Re: Select Max date in where statement

Re: Dupout Option in PROC SQL

Re: use macro variable in model statement

Re: SAS Macro Do Loops

Re: Sum up

Re: Format DATE9

Re: Search/replace with special characters in macro variable

Re: maintaining same variable value across observations

Re: function count -- specific data counted

Re: Stored Macro vars into a dataset

Re: Sum up

Re: Dupout Option in PROC SQL

Re: Need help with a clustering problem

Re: Please, please help! Trying to use an array statement to identify ...

Re: Count variable

Re: Count with Where

Re: Join/merge with dates

Re: Count with Where

Re: Data Step - Do Loop Question

Re: Count with Where

Re: Count with Where

Re: Count with Where