About jdwaterman91

jdwaterman91 · ‎11-01-2019

To piggyback off of the proposed solution @Kurt_Bremser has posted above, are there instances where your ID variable is missing? If so, that could throw off your macro variable string. Also, in the event you have to use single quotes: Proc SQL Noprint; Select Distinct "'" || ID || "'" Into :Inlist Separated By ',' From Have Where ID ^= ''; Connect to odbc (DSN = Warehouse_Name); Create Table Target_data As Select * From Connection to odbc ( Select a,b,c,d From Table.Column Where a in (&Inlist.) ); Disconnect From odbc; Quit;

jdwaterman91 · ‎10-25-2019

Depending on what your ultimate goal is, it may be better to combine the data into a single rows where the no_police value equals the no_cov value (This also is dependent on whether you have rows with the same values in either of these columns). However, to reach the output specified in your post, you can always create a macro variable to hold your no_police values and then check your no_cov variable against that. Proc SQL Noprint; Select Distinct No_Police Into :Check Separated By ',' From Test_Retain Where No_Police > 0; Quit; Data Want; Set Test_Retain; If No_Cov In (&Check.) Then No_Police = No_Cov; Run;

jdwaterman91 · ‎07-15-2019

Is there a limit to the number of columns proc report can write to Excel using ODS Excel (SAS 9.4 32 bit)? It appears as though I am hitting a limit at 25 columns. I haven't been able to find much on this topic online and was hoping somebody had more information on this. Thank you.

jdwaterman91 · ‎01-03-2019

Since you have datetime variables initially, they have to be converted to date variables for the format to correctly be applied. Data Want; Set Have; Array X _Numeric_; Do Over X; X = Datepart(X); End; Attrib _Numeric_ Format = Date9.; /* If you have other numeric variables in your dataset which you do not want to apply the format to, then specify each variable you want the format applied to. */ Run;

jdwaterman91 · ‎01-03-2019

I would suggest making your columns wider. I'm guessing that it is displaying that way in your dataset due to the columns not being wide enough.

jdwaterman91 · ‎01-03-2019

You can use an attrib statement in a data step. Attrib BDAY REcDate ReTDate Y201 Y22 Y716 Y133 Y876 Y7623 Y8613 Format = Date9.;

jdwaterman91 · ‎08-23-2018

Data Want; Set Have; Retain On_Off2 Cat2 Group2 Count; By Id Notsorted; Groups = Lag(Group); If First.Id Then Do; On_Off2 = On_Off; Cat2 = Category; Group2 = Group; Count = 1; End; Else Do; If Group ^= Groups Then Count + 1; If On_Off ^= On_Off2 Then On_Off2 = Catx('_', On_Off2, On_Off); If Category ^= Cat2 Then Cat2 = Catx('_', Cat2, Category); Group2 = Catx('_', Group2, Group); End; If Last.Id; If Count = 1 Then Group2 = Substr(Group2, 1, 1); Keep ID On_Off2 Cat2 Group2; Rename On_Off2 = On_Off Cat2 = Category Group2 = Group; Run;

jdwaterman91 · ‎08-07-2018

This is how I would produce the output you are looking for here: Data K; Set V (Keep = ID Sum Rapid Wash Poor Other); Length VQ $25; If Sum > 1 Then Do; If Rapid = 'X' Then VQ = 'Rapid'; If Wash = 'X' Then VQ = Catx(', ', VQ, 'Wash'); If Poor = 'X' Then VQ = Catx(', ', VQ, 'Poor'); If Other = 'X' Then VQ = Catx(', ', VQ, 'Other'); End; Keep ID Sum VQ; Run;

jdwaterman91 · ‎08-07-2018

Data Want; Set Have; FavFruit = Catx(' and ', of fruit1-fruit6); Keep Id FavFruit; Run; Does this accomplish what you were trying to accomplish?

jdwaterman91 · ‎03-23-2018

Piggybacking off of what KSharp posted, the following method should work for you: data have; input f1 $ f1_value f2 $ f2_value f3 $ f3_value; datalines; a 1 b 2 c 0 a 2 b 2 c 0 b 2 a 1 c 0 b 9 a 1 c 0 c 0 a 1 b 2 ; Run; Data Temp; Set Have; Col1 = Catt(f1, f1_value); Col2 = Catt(f2, f2_value); Col3 = Catt(f3, f3_value); Call Sortc(Col1, Col2, Col3); Run; Proc Sort Data = Temp Out = Want (Drop = Col1 Col2 Col3) Nodupkey; By Col1 Col2 Col3; Run;

jdwaterman91 · ‎07-05-2017

There are multiple ways of acquiring the sum of a column in SAS. Here are a couple of ways that could work for you. data Sums; Set Employee end = last; Retain Total; Total + Salary; keep Total; if last; Run; Proc SQL; Select Sum(salary) As Total_Sum From employee; Quit;

jdwaterman91 · ‎07-03-2017

Piggybacking off of what @MikeZdeb has, here are a couple of other options/things to consider. In case your data is not sorted, I would recommend sorting the data so that the date displayed is the earliest date (Assuming that is what you are looking for). Also you can create a macro variable in case your dataset has more than two records per member. The macro variable will resolve to the maximum number of records for a member (In the instance below it would be 3, since there are 3 records for member "101". For your initial dataset, it would be 2 since both members "101" and "102" have 2 records). I have added an additional line of data to your data for illustration purposes. data old; input member id date :date9.; format date date9.; datalines; 101 6 23Oct2017 /* Additional line of data */ 101 1 12Jun2017 102 3 14May2017 101 2 19Jul2017 103 5 24Jun2017 102 4 13May2017 ; run; Proc Sort data = old; By member id date; run; /* Creates your macro variable (n) */ Proc Sql noprint; select max(n) into :n from (select count(*) as n from old group by member); quit; /* Creates the output */ proc summary data=old nway; class member; output out=new (keep=member id: date_1) idgroup(out[&n] (id date)=); run;

jdwaterman91 · ‎06-29-2017

Then just use the sum function instead of the catt function

jdwaterman91 · ‎06-29-2017

Would something like this work for your situation? data Some; input Claim Suffix A $ B $ C $ D $ E $; datalines; 12589 1 vvg ijk ghy ilk opl 12589 2 rty ilk rtr erd owe ; run; Data Some2; Set Some; Array New A--E; Do _N_ = 1 to dim(New); ICD10 = New(_N_); Output; End; Keep Claim Suffix ICD10; Run;

jdwaterman91 · ‎06-26-2017

Ok. Try making this adjustment and see if that fixes your problem. proc sort data=have; by company date; run; data have; set want; by Company; Retain flag; If first.Company then flag = "N"; If (lag(Win_Lose) = "W" and lag(date) ^= date and lag(company) = company) then flag = "Y"; run;

Online Status	Offline
Date Last Visited	‎10-14-2024 06:46 PM

Re: Pulling Data from SQL Server Based on List of Values in SAS Table

Re: Use of RETAIN to grab informations in other observations

Proc Report Column Total

Re: Formats

Re: Formats

Re: Formats

Re: Multiple By Statements with First./Last.

Re: Create new variables with condition

Re: consolidating columns

Re: Eliminate duplicate record when factor and value mixed up

Re: Extract Information from variable

Re: How to make single column from different variables

Re: The COMPRESS function used with a modifier ?

Re: flagging for max and min values within a variable

Re: flagging previous events

Re: consolidating columns

Re: Eliminate duplicate record when factor and value mixed up

Re: how to convert a string variable so that it's exported in Excel as...

Re: transpose

Re: COV matrix incomplete

Re: Pulling Data from SQL Server Based on List of Values in SAS Table

Re: Use of RETAIN to grab informations in other observations

Proc Report Column Total

Re: Formats

Re: Formats

Re: Formats

Re: Multiple By Statements with First./Last.

Re: Create new variables with condition

Re: consolidating columns

Re: Eliminate duplicate record when factor and value mixed up

Re: How to calculate totals

Re: Adding one variable to dataset

Re: transpose

Re: How to make single column from different variables

Re: flagging previous events