I have an excel file that has a range of codes all in one row that I want to import into Sas

Reply
Contributor
Posts: 57

I have an excel file that has a range of codes all in one row that I want to import into Sas

I want to import this into and then for there to be a row for each code as all associated colums apply to every code.  so for example the range is 1-10 i want 10 rows in SAS but only one row in the excel file. eg:

excel file:

Baseline50360-50380Renal TransplantProtocol Annex 5

what i want  the SAS Dataset to look like is

Baseline50360Renal TransplantProtocol Annex 5
Baseline50361Renal TransplantProtocol Annex 5
Baseline50362Renal TransplantProtocol Annex 5
Baseline50363Renal TransplantProtocol Annex 5

  and so on.

Super User
Posts: 10,541

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

One basic approach would be 1) import the data into SAS and 2) manipulate that data for the desired output.

Assuming you have successfully accomplished step 1 some thing like this should work. I don't know your variable names so bear with me.

data want (drop= beginCode endCode CodeString) ;

     set have; /* this is the data you imported*/

     /* get the ranges of the codes to use, Assumes CodeString is the name of the variable containing 50360-50380*/

     BeginCode = input(scan(CodeString,1,best10.)); /* will read codes up to 10 characters*/

     EndCode = input(scan(CodeString,2,best10.));

     /* IF some of your code strings may contain a single value and not a range you need something like

     if missing(EndCode) then Code=input(Codestring,Best10.);

     else

     */

     do Code = BeginCode to Endcode;

          output;

     end;

run;

WARNING: If any of your codes may have decimals there will need to be a lot more work. Example: codestring value of 123.45 - 125.66

Contributor
Posts: 57

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

Thank you so much for your help. I tried the syntax you supplied above but I get the following errors when I run the program.

26   data want (drop= beginCode endCode Codeall) ;

27        set codes; /* this is the data you imported*/

28        /* get the ranges of the codes to use, Assumes CodeString is the name of the variable

28 ! containing 50360-50380*/

29        BeginCode = input(scan(Code,1,best10.)); /* will read codes up to 10 characters*/

                                        -------

                                        386

                                        76

30        EndCode = input(scan(Code,2,best10.));

                                      -------

                                      386

                                      76

ERROR 386-185: Expecting an arithmetic expression.

ERROR 76-322: Syntax error, statement will be ignored.

31        /* IF some of your code strings may contain a single value and not a range you need

31 ! something like

32        if missing(EndCode) then Code=input(Codestring,Best10.);

33        else

34        */

35        do Codeall = BeginCode to Endcode;

36             output;

37        end;

38   run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0

         observations and 8 variables.

WARNING: Data set WORK.WANT was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.03 seconds

I have managed to import ok ( the variable name containing the codes is  called "CODE")  It is a character variable. Any suggestions would be much appreciated. Thanks.

Super User
Posts: 10,541

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

Misplaced a parantheses

BeginCode = input(scan(Code,1),best10.);

EndCode = input(scan(Code,2),best10.);

Can't test code without data ...

Contributor
Posts: 57

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

Hello Mr Ballard,

Sorry to bother you again.  Firstly let me say thank you so much for all your help I ran the program and I am so happy it is almost perfect.  I have two little questions left that I know you will know and are probably obvious to most.  I have attached he excel file I am importing first and then the want.sas output created from your program.  As you can see single codes e.g. 0030 are missing from the want.sas output.  How can the program be amended to include these single codes in the want.sas output as well as I need these to be populated in the variable  codeall? Secondly I need to keep the exact number o of leading zeros in the codall variable as these are very specific to the individual codes and need to be retained.  A huge thanks in advance for any further help you are able to give me on this problem.  Kindest Regards,  Lisa.

Excel file Imported:

Lvl1GroupLvl2GroupLvl3GroupCodeTypeCodeDescriptorQualifierSource
BaselineRisk FactorInfctionICD9_DX001-002InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX0030InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX0031InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00320InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00321InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00322InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00323InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00324InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX00329InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5
BaselineRisk FactorSurgery_ICD9_PR007SurgeryProtocol Annex 5
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5
BaselineRisk FactorSurgery_CPT01214-01215SurgeryProtocol Annex 5
BaselineRisk FactorSurgery_CPT01402SurgeryProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX0202InfectionProtocol Annex 5
BaselineRisk FactorInfctionICD9_DX0203-0205InfectionProtocol Annex 5

Want.sas Output

Lvl1GroupLvl2GroupLvl3GroupCodeTypeCodeDescriptorQualifierSourceBeginCodeEndCodeCodeall
BaselineRisk FactorInfctionICD9_DX001-002InfectionProtocol Annex 5121
BaselineRisk FactorInfctionICD9_DX001-002InfectionProtocol Annex 5122
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5494
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5495
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5496
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5497
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5498
BaselineRisk FactorInfctionICD9_DX004-009InfectionProtocol Annex 5499
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818781
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818782
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818783
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818784
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818785
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818786
BaselineRisk FactorSurgery_ICD9_PR0081-0087SurgeryProtocol Annex 5818787
BaselineRisk FactorSurgery_CPT01214-01215SurgeryProtocol Annex 5121412151214
BaselineRisk FactorSurgery_CPT01214-01215SurgeryProtocol Annex 5121412151215
BaselineRisk FactorInfctionICD9_DX0203-0205InfectionProtocol Annex 5203205203
BaselineRisk FactorInfctionICD9_DX0203-0205InfectionProtocol Annex 5203205204
BaselineRisk FactorInfctionICD9_DX0203-0205InfectionProtocol Annex 5203205205
Contributor
Posts: 57

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

I have figured out how to keep single values and ranges now in the output:

data want ;

     set codes;

BeginCode = input(scan(Code,1),best10.);

EndCode = input(scan(Code,2),best10.);

if missing(EndCode) then do Codeall=input(Code,Best10.);output ;end;

  else

do Codeall = BeginCode to Endcode;

          output ;

     end;

run;

I just need help now with retaining the correct number of zeros!!!!  Many thanks again.

Super User
Super User
Posts: 6,502

Re: I have an excel file that has a range of codes all in one row that I want to import into Sas

Why are you trying to expand the list of values? Note that not all of the numbers in the range will be valid ICD-9 codes.

Why not just create upper and lower values for the range?   Then you can join with list of observed ICD-9 code values.

range='50360-50380';

length lower upper $5 ;

lower=scan(range,1,'-');

upper=scan(range,-1,'-');

Ask a Question
Discussion stats
  • 6 replies
  • 284 views
  • 0 likes
  • 3 in conversation