Here is a snapshot of some original data (the dataset is much larger)     Here is a snapshot of the proc contents for this dataset (side question- how can DOLLAR16.2 be an informat?)     I'm doing this work in EG, and what I'm trying to attempt to do either in a program or query builder is compare the values of ApprovedMemberPay and ApprovePatientPayAmount and return rows where they don't match, so when I try comparing them right off the bat without doing anything to the variables but simply using the condition "where ApprovedPatientPayAmount ne ApprovedMemberPay" I get a dataset showing the same values (below), which is the opposite of what I want.     how can I change formats/informats to compare the two values correctly? ... View more

Here is the code:   data work.teenstudents; set sashelp.class; where age > 12; run; data teenstudents2; set sashelp.class; where age > 14; run; data want; merge teenstudents(in=ts) teenstudents2(in=ts2); by name; keep name; if ts and not ts2; run; As you can see, I want all the names that are in teenstudents that are not in teenstudents2. The above merge works perfectly to accomplish that. But how do I do this in PROC SQL? I keep trying but I'm not getting the correct results. I left joined teenstudents with teenstudents2 on the condition that the names from each table do not equal each other, but I'm not getting the correct results... ... View more

  I have this dataset:   data have1; infile datalines; format MailOrderIndicator $1. FilledMonth $7.; input MailOrderIndicator $ FilledMonth $; datalines; N 2016M09 Y 2016M09 ; run; Now, I want to convert it to this:   data have2; infile datalines; format MailOrderIndicator 1. FilledMonth 1.; input MailOrderIndicator FilledMonth; datalines; 0 9 1 9 ; run;   Which is to say, I want to create a new dataset out of the first one, but I want N to equal 0, Y to equal 1, and 2016M09 to equal 9 (Character values to numeric values). I tried a custom format but can't seem to get it to work. For example, I tried this:   proc format; value $hello 'N'=0 'Y'=1; run; data want; set have1; format MailOrderIndicator $hello.; run;   Which replaces the N with 0 and Y with 1 but they are still character, whereas I want them to be numeric...     ... View more

Here is some sample data: data have; infile datalines; input Account Group M15201610 M15201611 M15201612 M30201601 M30201602; datalines; 1 1 0 2 2 2 . 0 0 1 2 2 2 . 3 3 0 3 1 2 1 ; run; Here is what I'm trying to do: %let criteria=(keep=M15201610-M30201602); data want; set have &criteria.; run; Now, I am getting the error: ERROR: Not all variables in the list M15201610-M30201602 were found.   When I put the range of variables as (keep= M15201610-M15201612), it works perfectly fine. The reason I'm doing this keep list in the macro variable is because my real data has many of these M variables and I don't want to have to list them out one by one. They represent dates at the middle of the month and end of month for each year and month. So M15201610 would be October 15, 2016. Is there any way I can get around this error? (don't ask why the dates are labeled this way, I didn't come up with the naming convention). ... View more

Here is some test data: data have; infile datalines; input RX_Chain $ Exclusion $ CLIENT $ ZIP; datalines; ALD854 N IL 22309 JX139D Y NE 21234 IL603X N MT 99087 AB2009 Y NM 20208 ; run; data have2; infile datalines; input Network $ Chain $; datalines; ALD854 00455 JX139D 00139 JX307E 00540 IL603X 00651 ; run; And here is some PROC SQL that will accomplish what I want: proc sql; create table want1 as select t1.RX_Chain, t1.CLIENT, t1.ZIP, (CASE WHEN t1.RX_Chain = t2.NETWORK THEN t2.CHAIN ELSE t1.RX_Chain END) format=$6. AS RX_Chain from have t1 left join have2 t2 on(t1.RX_Chain=t2.Network) where t1.Exclusion = 'N'; quit; But, I want to accomplish this with a merge. The trouble is with the naming: RX_Chain.   I attempted a start at the merge and it looks like this (after sorting initial datasets): data want; merge have(in=h) have2(in=h2 rename=(Network=RX_Chain)); by RX_Chain; if h and h2 then RX_Chain=Chain; else RX_Chain=RX_Chain; if h and Exclusion='N'; keep RX_Claim Client ZIP; run; However, I am not getting the same results as the PROC SQL. I know it has to do with the naming of RX_Chain and trying to assign a value to it but basically I'm trying to change the value of RX_Chain in have dataset to Chain in have2 dataset when RX_Chain = Network, or leave it as same value it was if not.   ... View more

Let's say I have this merge and the datasets have1 and have2 each have two variables: ID and Country.   data want; merge have1(in=h1) have2; by ID; if h1; run; I want to create a new variable based on the value of the variable Country in each dataset - a condition of whether Country from have1 = Country from have 2. In SQL, I would do this simply by using a case-when-end-as, but how would I do it in data step merge? I could rename the variables say to Country1 and Country2 then say if Country1=Country2, assign this value to new variable. But I don't wnat to keep these renamed variables - I want the original variable to stay. ... View more

I'm having trouble understanding why my proc sql and data step merge are not getting the same results. It seems to be off by one observation (I'm trying to convert my proc sql into a data step). Here is the full code:   data mydata; infile datalines; input list $ NDC discount; datalines; Nov 11 50 Dec 12 20 Nov 11 50 May 12 . Mar 11 30 ; run; data mydata2; infile datalines; input list $ NDC discount; datalines; Jan 11 40 Dec 12 20 Mar 10 50 May 12 . Mar 11 30 ; run; proc sql; create table x1 as select * from mydata t1 left join mydata2 t2 on(t1.list=t2.list); quit; proc sort data=mydata; by list; run; proc sort data=mydata2; by list; run; data x2; merge mydata(in=t1) mydata2; by list; if t1; run; Results of x1:     Results of x2:     The second observation from x1 is different from that of x2. Why is this? It must be something extremely simple - forgive me if I'm wasting your time. ... View more

data mydata; infile datalines; input list $ NDC discount; datalines; Nov 11 50 Dec 12 20 Nov 11 50 May 12 . Mar 11 30 ; run;   My data looks like this list NDC discount Nov 11 50 Dec 12 20 Nov 11 50 May 12 . Mar 11 30 And I want it to look like this: list ProductServiceID NDC discount Count_of_NDC Dec 12 12 20 1 Mar 11 11 30 1 Nov 11 11 50 2 That is, I want to create a count of NDC as well as create a new variable called ProductServiceID that is equal to NDC.   This Proc Sql will accomplish this: proc sql; create table mydata2 as select list, NDC as ProductServiceID, NDC, discount, count(NDC) as Count_of_NDC from mydata where discount ne . group by list,NDC,discount; quit; However, I want to do it using proc means or data step or a combination of the two. Please help. ... View more