Dsrountree Tracker

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Contact)

Dsrountree — Wed, 20 Sep 2017 12:26:21 GMT

@Reeza - Your suggestion seems more logical, but I never used countw in a data set.

Can you write a mock example of how it would parse the following:

John A Smith

John Smith

New York Times

Flex Gym

ABC Constructions

A contact (user) will either be two syllables (John Smith) or two syllables with a middle initial (John A Smith)

A Company/Business will show up as:

Two syllable - (Flex Gym, ABC Constructions), or three syllable (New York Times)

One parsed into these multiple sets it will have to be reviewed manually to validate where the mixture/blend of two syllable contact and company business took place.

I forsee this (hot mess) mixing items like Flex Gym and John Smith in the same column due to using a scan/countw.

Thanks in advance.

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Contact)

Dsrountree — Tue, 19 Sep 2017 23:05:59 GMT

This data is a hot mess...The problem with scan is a company can also be in the column and be 3 syllables.
Ex:ACME Marketing Company

Re: FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Contact)

Dsrountree — Tue, 19 Sep 2017 20:01:01 GMT

can you write out the data set?

FULLNAME to FIRST NAME; LAST NAME (via Contact Name or Business Contact)

Dsrountree — Tue, 19 Sep 2017 19:32:46 GMT

input fullname indicator;
datalines;
LEX HIBEAULT NM
LEX V TEORGE NM
AEX-SUT ENERGY CO

BOBBY N MAJUK CO
;

If I need to parse the following data and indicator = NM put scan 1 into first name and scan 2 into last name.

The problem is when a middle initial appears. I dont want to put V into last name.

It should put Teorge into last name and ignore the V.

Also Bobby N Majuk should be parsed as Bobby first name and Majuk last name. Even when the indicator is CO

I tried to write a data set with if then based on evaluating the full name to see if a character after the first scan was = 1, then treat it as first name last name and move each section from full name to first name then last name.

Had no luck.

Re: Finding Like Values and Assigning them to a DATASET

Dsrountree — Tue, 12 Sep 2017 15:26:53 GMT

Nicely done....

But quick question on data _NULL_;

If I have several other columns,

IE: First Name, Last Name, Address.

Will that information also carry over to table A, B, C?

Re: Finding Like Values and Assigning them to a DATASET

Dsrountree — Tue, 12 Sep 2017 13:59:39 GMT

If I was only looking at a distinct value like zipcode then I could use code like that.

The problem is I need to evalute the value based on a given position and separate accordingly.

Any suggestions...

Finding Like Values and Assigning them to a DATASET

Dsrountree — Tue, 12 Sep 2017 13:05:14 GMT

What is the easiest way to parse a data set and create multiple data sets based on a given value?

Example:

Let’s use telephone numbers and look for all values found in PHONENUMBER beginning with

631 and put them in data set A

516 and put them in data set B

212 and put them in data set C

Data:

631 555 5555

631 666 6666

516 999 9999

516 888 8888

212 444 4444

212 444 5555

Problem:

You don’t know the first 3 values – If I did – I would write a substring/do loop to read the first 3 of the field “PHONENUMBER” and tell it to read through the table and when it finds a matching value “substr (phonenumber,1,3) write it out to the associated table.

What function would you use to search the field PHONENUMBER and look for all like values from a specific position and place those values in a separate data set?

This is without defining the values to look for since the list of area codes is quite large.

All help is greatly appreciated – I haven’t been in the SAS world for over 2 years so I’m a little rusty – Thanks in advance

Proc Transpose - Removing dupes

Dsrountree — Thu, 02 Oct 2014 00:49:41 GMT

The following data was transposed:

PBP_Number	CategoryCode	CategoryDescription	Benefit	sentences_sort_order	Star_Rating	Enrollment	indx	idlabel	Taborder
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX19-0	21
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Cleaning (for up to 1 every six months): You pay nothing	623	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Dental x-ray(s) (for up to 1 every six months): You pay nothing	656	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	424	8	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	424	8	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	12682	7	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Oral exam (for up to 1 every six months): You pay nothing	722	3.5 out of 5 stars	12682	7	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	7	Dental Services	Preventive dental services:	622	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	424	1	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing	802	3.5 out of 5 stars	12682	2	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	424	4	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Diabetes self-management training: You pay nothing	834	3.5 out of 5 stars	12682	3	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	424	6	2015~Benefit Plan Increased Benefits Plan (HMO)~424~3.5 out of 5 stars~HXXXXX-019-0	11
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	21
HXXXXX-019-0	8	Diabetes Supplies and Services	Therapeutic shoes or inserts: You pay nothing	866	3.5 out of 5 stars	12682	5	2015~Benefit Plan Increased Benefits Plan (HMO)~12682~3.5 out of 5 stars~HXXXXX-019-0	11

This data contains duplicate values in the BENEFIT column after I’ve indexed the columns to make sure the data is sorted according to sentences_sort_order.

I need to remove the duplicates much like you do in Excel after the columns are created. Other PBP_Numbers when transposed don’t always contain duplicates in the BENEFIT column.

PROC TRANSPOSE DATA = Benefits Plan_HXXXXX_15 OUT = Benefits Plan_HXXXXX_15 PREFIX = ID_ ;

BY CategoryCode CategoryDescription indx;

var Benefit;

id PBP_Number ;

idlabel idlabel;

RUN;

PROC REPORT DATA = Benefits Plan _HXXXXX_15 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Contract Year~Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

Contract Year Plan Name Current Enrollment Star Rating Contract Number/PBP	2015 Benefits Plan (HMO) 424 3.5 out of 5 stars HXXXXX-019-0

Dental Services	Preventive dental services:
	Preventive dental services:
	Cleaning (for up to 1 every six months): You pay nothing
	Cleaning (for up to 1 every six months): You pay nothing
	Dental x-ray(s) (for up to 1 every six months): You pay nothing
	Dental x-ray(s) (for up to 1 every six months): You pay nothing
	Oral exam (for up to 1 every six months): You pay nothing
	Oral exam (for up to 1 every six months): You pay nothing





Diabetes Supplies and Services	Diabetes monitoring supplies: You pay nothing
	Diabetes monitoring supplies: You pay nothing
	Diabetes self-management training: You pay nothing
	Diabetes self-management training: You pay nothing
	Therapeutic shoes or inserts: You pay nothing
	Therapeutic shoes or inserts: You pay nothing

I know when I added the additional data IE: Star_Rating & Enrollment via a left outer join this created my dups.

The problem is I can't do an inner since I need to make sure all the value(s) found in the left table remain in my extract and only pull a value when it finds a matching PBP_Number.

Re: Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 13:04:48 GMT

Reading about it via Google - SAS.com - a little above my head but I understand why it's useful.

Since I'm new to this I write the SQL first and test the output to ensure data integrity then if needed create the macro.

Re: Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 12:55:01 GMT

Thank you so much for the prompt reply....

Keep in mind I'm new to SAS - 1.7 years total.

What is options mlogic mprint symbolgen?

Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 12:40:00 GMT

Prior to introducing the macro to this part of the code – each section would create an XML file based on the sheet_name=’ ‘;

Now that I’ve created a macro that reads each data set and performs the function for all eight datasets the naming isn’t working.

Perhaps it’s something simple missing between the ‘ ‘ – ex: ‘&&tb4’ right now it writes each tab for the output data as tb4, tb4 2, tb4 3, …… incrementing by 1 after the tb4.

I’m not sure – New Jack SAS Guy

ODS TAGSETS.EXCELXP file='/windows/Infodata/DRountree/CAB/2014_2015_Competitive_Analysis_HMO_NYC.xml';

*** Define macro3 ***;

%macro macro3(tb4);

ODS TAGSETS.EXCELXP options(sheet_interval='table' suppress_bylines='yes' sheet_name= '&tb4');

PROC REPORT DATA = &tb4 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

%mend macro3;

*** Run macro3 ***;

%macro3(Aetna_14);

%macro3(Empire_BCBS_14);

%macro3(Healthfirst_14);

%macro3(UH_AARP_14);

%macro3(Aetna_15);

%macro3(Empire_BCBS_15);

%macro3(Healthfirst_15);

%macro3(UH_AARP_15);

ODS _ALL_ CLOSE;

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Wed, 27 Aug 2014 14:15:34 GMT

I wasn't able to use the #byval function since I removed the BY from my Proc Report. I still need to learn (MACROS) so the redundant code below would be written once and call for each of the DATA= and create the .XML using the correct names from each DATASET.

ODS TAGSETS.EXCELXP file='/windows/Infodata/DRountree/CAB/Competitive_Analysis_HMO_Nassau.xml'

options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval1');

PROC REPORT DATA = Empire_H3370 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

PROC REPORT DATA = Healthfirst_H3359 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

PROC REPORT DATA = HealthPlus_H6181 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

PROC REPORT DATA = Liberty_H3337 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

PROC REPORT DATA = VNSNY_H5549 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

PROC REPORT DATA = WellCare_H3361 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

ODS _ALL_ CLOSE;

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Wed, 27 Aug 2014 12:11:43 GMT

You two are great - Thanks for all your help and guidance.

Report is about 80% done and once complete I'll look into macro processing to clean up the repetitive section of code.

Who would of thought it was as easy as placing the report code inside the ODS open and close.

Anything I place in between is added to one Excel workbook, so now I'm reading up on naming the tabs based on the datasheet that imported.

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Mon, 25 Aug 2014 16:04:01 GMT

For a new jack this is quite the challenge - but I love learning so it's ok.

Once I'm done reading up on both suggestions I'll provide an update and close this one out.

Thank you so much data_null and Cynthia...

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Mon, 25 Aug 2014 13:19:20 GMT

Good morning data_null;

I created a mock sample of what I'm trying to accomplish and loaded it to Box.com (sharing site) https://app.box.com/s/mmapprud9sve9v1p77xr

In the mock up you see on one tab PLAN_A vs. PLAN_B and on the second tab you see PLAN_B vs. PLAN_B.

In our prior conversation we solved how to take the data and convert it to a point where I can do a side by side comparison of various health plans per category via TRANSPOSE and PROC REPORT.

Now the additional requirement is to build a complex report with the information based on different scenarios:

IE: One XLS might contain all HMO PLANS for Suffolk County and provide a side by side for PLAN_A vs each competitor. These are separated tab by tab,

which means each competitor has one tab to itself per the comparison with PLAN_A (This logic I'm programming and as stated above was normal done by creating a separate

dataset for each compare then exporting to the same XLS but on a different tab).

The problem is how do I perform the same task with the output from proc report? I don't mind programming each data set with it's own TRANSPOSE/REPORT.

If you think of all the Health Plans in NY - I'm comparing PLAN_A per each County (which creates a separate XLS for each one, Suffolk, Nassau, etc) and within each XLS are all the competitors for PLAN_A. Each one on it's own tab. PLAN_A vs PLAN_B (tab 1) PLAN_A vs PLAN_C (tab 2), etc....

When I look at last year report that was created manually it was 10 XLS documents with over 20 tabs in each XLS.

This is why I need to find a way to automate the output from PROC TRANSPOSE / PROC REPORT into multiple XLS on multiple tabs based on the DATASET I define within my PROC SQL to parse the data.

Pheeeeeeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwwwwwwwwwwww

Derrick Rountree │ Sr. Business Data Analyst, Medicare Products │ Office (646) 447-5992

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Fri, 22 Aug 2014 19:20:11 GMT

You helped me with the first part of this (Proc Transpose & Proc Report)

The problem is I can't use the output generated from Proc Report - since it's not in the same layout as the FINAL ODS excel file created.

I trouble shooted by adding an OUT= to the Proc Report and this was obviously not the correct direction since the output contained_NAME_, etc and it's not in the same layout as the report or the XML file.

Sorry to pull you into this mess - Enjoy your weekend

PROC REPORT DATA=Empire_X3370 OUT=Empire_X3370 spanrows nowd split='~';

MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Fri, 22 Aug 2014 17:38:46 GMT

I currently use this process to append multiple datasets to one excel document at any given location:

the two data sets are exported and become two seperate tabs in the "&folder.CPSC_Enrollment__&dt..xls"

%let pc = NYWSWXXXXXX;

%let folder = C:\Users\drountree\;

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

proc export

data = Summary_By_Org_HMO

outfile = "&folder.CPSC_Enrollment__&dt..xls"

dbms = excelcs replace;

sheet = "Summary_By_Org_HMO";

SERVER = "&pc";

run;

proc export

data = Summary_By_Org_PPO

outfile = "&folder.CPSC_Enrollment__&dt..xls"

dbms = excelcs replace;

sheet = "Summary_By_Org_PPO";

SERVER = "&pc";

run;

Now this is where it gets fun. I understand fully the section above and I'm able to code multiple datasets

meeting various requirements and have them output to various .XLS on different tabs based on a given criteria.

MY PROBLEM:

I need to take a newly learned process via data_null; (ODS tagsets subset PROC REPORT) and take the output Transposed file

and export each one to the same xls as above based on a given condition.

PROC TRANSPOSE DATA=Empire_X3370 OUT=Empire_X3370 PREFIX=ID_;

SYSECHO "PROC TRANSPOSE DATA=ABRIDGED OUT=ABRIDGED PREFIX=ID_;";

BY CategoryCode CategoryDescription indx;

var Benefit;

id PBP_Number;

idlabel idlabel;

RUN;

ods tagsets.ExcelXP file= '/windows/Infodata/DRountree/CAB/Empire_X3370.xml';

PROC REPORT DATA=Empire_X3370 spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

ods tagsets.excelxp close;

The above code creates one data set Empire_X3370 and exports a XML file to the specified location.

I'm creating several different comparissions and need to export Blue_X5590 to the same file but in a different tab.

Keep in mind if this wasn't a PROC REPORT ODS XML output then this would be easy and I could use the first set of code above:

%let pc = NYWSWXXXXXX;

%let folder = C:\Users\drountree\;

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

proc export

data = Empire_X3370

outfile = "&folder.CPSC_Enrollment__&dt..xls"

dbms = excelcs replace;

sheet = "Empire_X3370";

SERVER = "&pc";

run;

proc export

data = Blue_X5590

outfile = "&folder.CPSC_Enrollment__&dt..xls"

dbms = excelcs replace;

sheet = "Blue_X5590";

SERVER = "&pc";

run;

The problem is the output from the PROC REPORT...

Re: Data Set Manipulation

Dsrountree — Thu, 21 Aug 2014 18:57:37 GMT

How do I get the full number to display in the category Description?

See longer numbers below:

data set1 ;
   input (CONTRACT_ID PLAN_NAME) (:$20.);
   cards;
R5678-012-01 PLAN_B
X12345-145-01 PLAN_A
X5678-888-00 PLAN_B
;;;;

data set2 ;
   input (CONTRACT_ID ENROLLMENT) (:$20. :F12.);
   cards;
R5678-012-01 112000
X12345-145-01 111000
X5678 112000
;;;;

data set3 ;
   input (CONTRACT_ID STAR_RATING) (:$20.);
   cards;
R5678-012-01 3.5
X12345-145-01 4
X5678-888-00 5
;;;;

Re: Data Set Manipulation

Dsrountree — Wed, 13 Aug 2014 18:04:05 GMT

FYI:

Management loved the first draft (Thanks data_null_; & Cynthia@SAS) , but I need to make a few additional changes to the output...

Suppose I had three other data sets that needed to be added to the header (SEE GREEN) portion only of the PROC REPORT:

PLANNAME - DATA SET 1 - I've created a mock of the this data set below
STAR RATING - DATA SET 2 - I've created a mock of the this data set below
ENROLLMENT - DATA SET 3 - I've created a mock of the this data set below

Can they be added to the top of Report?

I call this trial by fire - Normally this process is done manually and takes 2 weeks to complete with over 100 side by side plan comparisons...

For simplicity the mock data is all CHAR - the numbers will be NUMERIC or CHAR in real data pull:

data set1 ;

input (CONTRACT_ID PLAN_NAME) (:$20.);

cards;

X12345 PLAN_A

X5678 PLAN_B

;;;;

data set2 ;

input (CONTRACT_ID ENROLLMENT) (:$20.);

cards;

X12345 111000

X5678 112000

;;;;

data set3 ;

input (CONTRACT_ID STAR_RATING) (:$20.);

cards;

X12345 4

X5678 5

;;;;

Re: Data Set Manipulation

Dsrountree — Tue, 12 Aug 2014 19:40:18 GMT

If the X: value has another variable that starts with an "R" how would you modify the PROC REPORT to handle that condition?

proc report data=hippo spanrows nowd;
   columns CategoryCode CategoryDescription x:;
   Define categoryCode / order order=internal noprint;
   define CategoryDescription / order;
   define x: / display;
   compute after categorycode;
      line ' ';
      endcomp;
   run;