DATA Step, Macro, Functions and more

Proc Format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Proc Format

Hello.  I am trying to put a dataset in the format that can be used by another application.  I've gone through several proc sql steps and are down to the final step. I've spent 8 hours on this myself and am thoroughly frustrated and stuck.

The dataset that has the data is folder.cpt_expan_annualcodeupdate_fix.

The final format needs to be the following:

RBRVSYear num

,SubspecialtyId num

,FSSSubspecialtyId num

,ProcedureCodeCategory Char(1)

,ProcedureCode Char(5)

,ModifierCode Char(2)

,BlendedSiteOfService Char(1)

,CignaWorkFactor num format=6.3

,CignaFacilityPracticeFactor num format=6.3

,CignaNonFacilityPracticeFactor num format=6.3

,CignaMalPracticeFactor num format=6.3

,MedicareWorkFactor num format=6.3

,MedicareFacilityPracticeFactor num format=6.3

,MedicareNonFacilityPracticeFac num format=6.3

,MedicareMalpracticeFactor num format=6.3

,LabAmount num format=7.2

I've tried to create a table with the above format and use a proc append with the force option, but I must not have the syntax correct.

proc append base = folder.pst_reference_cptrbrvs_work data = folder.pst_reference_cptrbrvs

FORCE;

run;

Any help someone can provide I'd greatly appreciate it.


Accepted Solutions
Solution
‎04-19-2016 11:58 PM
Occasional Contributor
Posts: 12

Re: Proc Format

Astounding(Super User), Thank you for the information data;length...;set;format...;.  I was able to use this to fix the dataset before exporting to Access.  Everyone, thank you so much!

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Proc Format

Are you trying to append to a SAS data set or something in another DBMS?

HOW are you attempting to make the table? Show code if possible.

 

If the target dataset is a SAS data set show us the results of proc content.

 

The force option means that variables with the same name and type will append to a dataset if some other characteristic such as format, length, label differ. It does not mean that variables that are in the DATA data set that are not in the Base data will be in the resulting data file.

Super User
Posts: 5,082

Re: Proc Format

Just to echo the need for more information ...

 

When you say "another application" does that mean another SAS program or a different language entirely?

 

Does this data need to be combined with additional existing data, or is it just a matter of processing the current set of data by another application?

 

It is very possible that you're very close to a final solution here.

Occasional Contributor
Posts: 12

Re: Proc Format

Thanks for the quick response.

I created a SAS dataset on my local drive from information from a DB2 Mainframe.

It is my created SAS dataset that I'm not trying to format after some additional manipulations.

The Proc Content of my original SAS Dataset is below.

 

The code that I used to create a new table that has the necessary format is:

 

proc sql;

create table folder.pst_reference_cptrbrvs_work

(

RBRVSYear num

,SubspecialtyId num

,FSSSubspecialtyId num

,ProcedureCodeCategory Char(1)

,ProcedureCode Char(5)

,ModifierCode Char(2)

,BlendedSiteOfService Char(1)

,CignaWorkFactor num format=6.3

,CignaFacilityPracticeFactor num format=6.3

,CignaNonFacilityPracticeFactor num format=6.3

,CignaMalPracticeFactor num format=6.3

,MedicareWorkFactor num format=6.3

,MedicareFacilityPracticeFactor num format=6.3

,MedicareNonFacilityPracticeFac num format=6.3

,MedicareMalpracticeFactor num format=6.3

,LabAmount num format=7.2

 );

quit;

 

Here is my proc append code:

 

proc append base = folder.pst_reference_cptrbrvs_work data = folder.pst_reference_cptrbrvs

FORCE;

run;

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 7 9 11 10 8 3 16 13 15 14 12 6 5 4 1 2
BLENDEDSITEOFSERVICEChar1$1.$1.BLENDEDSITEOFSERVICE
CIGNAFACILITYPRACTICEFACTORNum88.38.3CIGNAFACILITYPRACTICEFACTOR
CIGNAMALPRACTICEFACTORNum88.38.3CIGNAMALPRACTICEFACTOR
CIGNANONFACILITYPRACTICEFACTORNum88.38.3CIGNANONFACILITYPRACTICEFACTOR
CIGNAWORKFACTORNum88.38.3CIGNAWORKFACTOR
FSSSubspecialtyIdNum8   
LABAMOUNTNum87.27.2LABAMOUNT
MEDICAREFACILITYPRACTICEFACTORNum88.38.3MEDICAREFACILITYPRACTICEFACTOR
MEDICAREMALPRACTICEFACTORNum88.38.3MEDICAREMALPRACTICEFACTOR
MEDICARENONFACILITYPRACTICEFACNum88.38.3MEDICARENONFACILITYPRACTICEFAC
MEDICAREWORKFACTORNum88.38.3MEDICAREWORKFACTOR
MODIFIERCODEChar2$2.$2.MODIFIERCODE
PROCEDURECODEChar5$5.$5.PROCEDURECODE
PROCEDURECODECATEGORYChar1$1.$1.PROCEDURECODECATEGORY
RBRVSYEARChar4$4.$4.RBRVSYEAR
SubspecialtyIdNum8   
Super User
Super User
Posts: 6,500

Re: Proc Format

[ Edited ]

You can't just change the TYPE of a variable like RBRVSYEAR from character to number. 

So if your source table has RBRVSYEAR as $4 and your want it as a number then you need do some renaming along the way.

 

 

data want ;
  length RBRVSYEAR 8 ..... ;
  set have (rename=(RBRVSYEAR=RBRVSYEAR_char ....)) ;
  RBRVSYEAR = input(RBRVSYEAR,4.);
  drop RBRVSYEAR_char ;
  ...
run;

 

Occasional Contributor
Posts: 12

Re: Proc Format

Here is my attempt without the renaming that Tom has indicated that I need.   I'm not sure how to incorporate Tom's information.

 

data folder.pst_reference_cptrbrvs_final;
LENGTH
RBRVSYear $4.
SubspecialtyId 3
FSSSubspecialtyId 3
ProcedureCodeCategory $1.
ProcedureCode $5.
ModifierCode $2.
BlendedSiteOfService $1.
CignaWorkFactor 8
CignaFacilityPracticeFactor 8
CignaNonFacilityPracticeFactor 8
CignaMalPracticeFactor 8
MedicareWorkFactor 8
MedicareFacilityPracticeFactor 8
MedicareNonFacilityPracticeFac 8
MedicareMalpracticeFactor 8
LabAmount 8;
set folder.cpt_expan_annualcodeupdate_fix;
format
CignaWorkFactor 6.3
CignaFacilityPracticeFactor 6.3
CignaNonFacilityPracticeFactor 6.3
CignaMalPracticeFactor 6.3
MedicareWorkFactor 6.3
MedicareFacilityPracticeFactor 6.3
MedicareNonFacilityPracticeFac 6.3
MedicareMalpracticeFactor 6.3
LabAmount 7.2;
run;

Super User
Posts: 5,082

Re: Proc Format

If I interpret this correctly, you have an existing SAS data set named folder.cpt_expan_annualcodeupdate_fix

 

That SAS data set needs to remain as a SAS data set, yet been transformed into the proper format.

 

If that's the case, a four-line DATA step would do it (although some statements might be lengthy):

 

data folder.transformed;

LENGTH ...;

set folder.cpt_expan_annualcodeupdate_fix;

FORMAT ...;

run;

 

The LENGTH statement should assign the variables their proper length, in the order that the variables should appear in the final data set.  For example, the beginning of the LENGTH statement might read:

 

length RBRVSYear 8

Subspecialtyld 8

FSSSubspecialtyld 8

ProcedureCodeCategory $ 1

...;

 

The SET statement could, optionally, subset the variables:

 

set folder.cpt_expan_annualcodeupdate_fix (keep=list of variables to keep);

 

And the FORMAT statement would add the formats:

 

Format CignaWorkFactor 6.3

CignaFacilityPracticefactor 6.3

...

;

 

You don't need to mention every variable in the FORMAT statement ... just those that require a format.

Occasional Contributor
Posts: 12

Re: Proc Format

If the list of variables(columns) have some that are numeric and some that are text, which variables should be listed under the "length" and which variables should be listed under the "format"?  

Super User
Posts: 5,082

Re: Proc Format

All variables get listed in the LENGTH statement.  That's because I'm assuming the "formatting" of the data set includes the ordering of the columns, something that the LENGTH statement controls if it appears early enough in the DATA step.

 

The FORMAT statement names only the variables that receive a format, supplying both the variable name and the format.

Occasional Contributor
Posts: 12

Re: Proc Format

Hi everyone,

I want to thank everyone for their time.  I was making something much harder than it needed to be.  All I needed to do was to change the variable RBRVSYear from text to numeric.  After all of my attempts, literally 16 hours, and the feedback from this group I got it done in 10 minutes:  Here is the code that worked.

 

proc sql;
create table folder.pst_reference_cptrbrvs_final as
(select
input(RBRVSYear, 4.) as RBRVSYear
,SubspecialtyId
,FSSSubspecialtyId
,ProcedureCodeCategory
,ProcedureCode
,ModifierCode
,BlendedSiteOfService
,CignaWorkFactor
,CignaFacilityPracticeFactor
,CignaNonFacilityPracticeFactor
,CignaMalPracticeFactor
,MedicareWorkFactor
,MedicareFacilityPracticeFactor
,MedicareNonFacilityPracticeFac
,MedicareMalpracticeFactor
,LabAmount
from folder.pst_reference_cptrbrvs

);
quit;

Solution
‎04-19-2016 11:58 PM
Occasional Contributor
Posts: 12

Re: Proc Format

Astounding(Super User), Thank you for the information data;length...;set;format...;.  I was able to use this to fix the dataset before exporting to Access.  Everyone, thank you so much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 426 views
  • 0 likes
  • 4 in conversation