BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Laurel
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Laurel
Obsidian | Level 7

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

10 REPLIES 10
ballardw
Super User

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
PROC Star

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
Obsidian | Level 7

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   
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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