Proc Format

[Laurel]

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.

[ballardw]

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.

[Astounding]

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.

[Laurel]

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

BLENDEDSITEOFSERVICE	Char	1	$1.	$1.	BLENDEDSITEOFSERVICE
CIGNAFACILITYPRACTICEFACTOR	Num	8	8.3	8.3	CIGNAFACILITYPRACTICEFACTOR
CIGNAMALPRACTICEFACTOR	Num	8	8.3	8.3	CIGNAMALPRACTICEFACTOR
CIGNANONFACILITYPRACTICEFACTOR	Num	8	8.3	8.3	CIGNANONFACILITYPRACTICEFACTOR
CIGNAWORKFACTOR	Num	8	8.3	8.3	CIGNAWORKFACTOR
FSSSubspecialtyId	Num	8
LABAMOUNT	Num	8	7.2	7.2	LABAMOUNT
MEDICAREFACILITYPRACTICEFACTOR	Num	8	8.3	8.3	MEDICAREFACILITYPRACTICEFACTOR
MEDICAREMALPRACTICEFACTOR	Num	8	8.3	8.3	MEDICAREMALPRACTICEFACTOR
MEDICARENONFACILITYPRACTICEFAC	Num	8	8.3	8.3	MEDICARENONFACILITYPRACTICEFAC
MEDICAREWORKFACTOR	Num	8	8.3	8.3	MEDICAREWORKFACTOR
MODIFIERCODE	Char	2	$2.	$2.	MODIFIERCODE
PROCEDURECODE	Char	5	$5.	$5.	PROCEDURECODE
PROCEDURECODECATEGORY	Char	1	$1.	$1.	PROCEDURECODECATEGORY
RBRVSYEAR	Char	4	$4.	$4.	RBRVSYEAR
SubspecialtyId	Num	8

[Tom]

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;

[Laurel]

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;

[Astounding]

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.

[Laurel]

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"?  

[Astounding]

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.

[Laurel]

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;

[Laurel · Accepted solution]

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!