topic Re: Format Variable based on description in csv file in SAS Procedures

Format Variable based on description in csv file

bikash — Wed, 11 Feb 2015 15:20:26 GMT

I have diagnosis variable in my dataset which is ICD Code. I want to format Diagnosis variable in CCS Category which available in csv format (attached).

Attached csv file has 3 columns: 1. ICD-Code ( Diagnosis in my data-set), 2. CCS Category (I want Diagnosis formatted in CCS Category), 3. CCS Category Description (I want CCS Category to be labeled with this information)

Below is how csv file column looks like. 15,000 ICD Codes are grouped in clinically meaningful 200 (approx) categories and I am trying to format Diagnosis Variable in my data set in those Categories. How can I do it?

Thanks.

'ICD-9-CM CODE'

'CCS CATEGORY'

'CCS CATEGORY DESCRIPTION'

'3220 '	'76 '	'Meningitis'
'3221 '	'76 '	'Meningitis'
'3222 '	'76 '	'Meningitis'
'3229 '	'76 '	'Meningitis'
'0361 '	'77 '	'Encephalitis'
'0462 '	'77 '	'Encephalitis'
'0498 '	'77 '	'Encephalitis'
'0499 '	'77 '	'Encephalitis'
'0520 '	'77 '	'Encephalitis'
'0543 '	'77 '	'Encephalitis'
'0550 '	'77 '	'Encephalitis'
'05601'	'77 '	'Encephalitis'
'05821'	'77 '	'Encephalitis'
'05829'	'77 '	'Encephalitis'
'0620 '	'77 '	'Encephalitis'

Re: Format Variable based on description in csv file

RW9 — Wed, 11 Feb 2015 15:32:32 GMT

Well, firstly you will need to import the CSV file into a dataset. Once you have it in a dataset you can do a few things. First one most people will recommend is to create format library from it, look up proc format in the SAS help. From the above ID column is value, decode is in the last column. You can then apply this format to you data item. This method doesn't change the original data only the way it is displayed.

Another way is to use the data as a lookup:

proc sql;

create table WANT as

select A.*,

(select CCS_CAT from CODE_LIST where ICD9=A.ICD9) as FORMATTED_VALUE

from HAVE;

quit;

In the above statement I keep all the original data and then lookup the result value based on the data value into a new variable formatted_value. The benefit of this is that you gain a column which has the formatted value as the data, nothing hidden. So really depends on what you want to do with the data/where its going to .

Re: Format Variable based on description in csv file

bikash — Wed, 11 Feb 2015 15:44:00 GMT

Thanks RW9,

By "..firstly you will need to import the CSV file into a dataset"; you mean should I make csv file separate SAS dataset or combine csv columns in existing dataset?

Re: Format Variable based on description in csv file

RW9 — Wed, 11 Feb 2015 15:47:21 GMT

Just import the CSV to a new dataset:

data code_list;

infile "thecsv.csv" dlm=",";

input icd9 $ css $ csscat $;

run;

Re: Format Variable based on description in csv file

bikash — Wed, 11 Feb 2015 16:30:35 GMT

OK, I created SAS data-set of csv file. How do I create format library from it. I see proc format in the SAS help only has way to create library based on value not by SAS data-set. How do I format with SAS data-set? Could you suggest codes too.

Thanks.

Re: Format Variable based on description in csv file

Reeza — Wed, 11 Feb 2015 16:42:54 GMT

See Example 8 in this paper:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

Re: Format Variable based on description in csv file

Ksharp — Thu, 12 Feb 2015 09:40:31 GMT

Another way is Hash Table .

Re: Format Variable based on description in csv file

bikash — Thu, 12 Feb 2015 17:29:23 GMT

I don't know what this is but it seems to work with modification.It has used cntlin function. I found this on Agency for Health Care Research website. Still I am not able to label CCS group.Thanks to all.

Filename inraw "c:\tools\ccs\$DXREF 2013.csv" ;
/*****************************************/
/* Make a SAS format*/
/*****************************************/ data new;
      infile inraw dsd dlm=',' end = eof firstobs=3 missover;
         input start : $char5.
              Oldccs : $char4.
              Value1 : $char70.
              Value2 : $char70.
              Label : $char4.
            Value3 : $char70.
              ;
         retain hlo "   ";
      fmtname = "$ccs" ;
      type = "   " ;
      output;

      if eof then do ;
         start = "   " ;
         vlabel = "   " ;
         hlo = "o";
         output ;
      end ;
run;
proc format cntlin = new ;
run;
/*****************************************/
/* Reassign CCS */
/*****************************************/
data test;
      Set data;
      Ccs = put(ecode Diagnosis,$ccs.)
run;

Re: Format Variable based on description in csv file

Reeza — Thu, 12 Feb 2015 20:53:50 GMT

bikash wrote:

Still I am not able to label CCS group.

You'll need to provide more details than that.

Re: Format Variable based on description in csv file

bikash — Thu, 12 Feb 2015 21:25:00 GMT

Reeza,

If you see attached csv file, it has 3 columns: Icd9 Code, CCS Group and CCS Description. With above syntax, I can group ICD Code in CCS group but cannot label it with CCS Description..

e.g. ICD code '3220', or '3221' is formatted as 76 (CCS Group) in proc freq result but I wanted it as 'Meningitis' in result.

Re: Format Variable based on description in csv file

Tom — Thu, 12 Feb 2015 21:36:58 GMT

You could make more than one format from the data.

So one could map ICD9 -> CCS code

Another could map ICD9 -> CCS Description

Another could map ICD9 -> CCS code + CCS Description.

filename zipfile zip 'C:\Downloads\$dxref 2015.csv.zip';

data ccs ;

infile zipfile('$dxref 2015.csv') dsd firstobs=2 truncover ;

length diag_cd $5 ccs_cd $4 ccs_desc $70 ;

input diag_cd ccs_cd ccs_desc ;

if ccs_cd ne ' ';

run;

data cntlin ;

length fmtname $32 type $1 start $80 label $80 ;

keep fmtname type start label ;

type='C';

set ccs ;

fmtname='CCS';

start=diag_cd;

label=ccs_cd ;

output;

fmtname='CCSD';

start=diag_cd;

label=ccs_desc ;

output;

fmtname='CCSB';

start=diag_cd;

label=catx(' - ',ccs_cd,ccs_desc) ;

output;

run;

proc sort nodupkey ; by fmtname start label ; run;

proc format cntlin=cntlin; run;

data test;

input diag_cd $5. ;

ccs=put(diag_cd,$ccs.);

ccsd=put(diag_cd,$ccsd.);

ccsb=put(diag_cd,$ccsb.);

put (_all_) (=);

cards;

01080

7141

;;;;

Re: Format Variable based on description in csv file

ballardw — Thu, 12 Feb 2015 21:57:37 GMT

For example I have formats that map my state's county codes to:

The county name

a variety of geographical regions such as Health Districts

translate to a different coding scheme used by some applications

A multilabel format that for the applications that support multilabel formats such as Proc Tabulate and Report, geographic region and county name

The best way to I found to learn to make CNTLIN data sets to make format is to examine a CNTLOUT data set.

Create a format (or multiple formats) with a few values similar to what you need. Add the option CNTLOUT= <some dataset name>;

When you run proc format that data set will be created with the information you need. Make a set that looks like that an use it as a CNTLIN dataset.

Important: Proc formats expect specific variable names for the bits it needs AND the type. Also the CNTLIN dataset must have all of the FMTNAME values together, best is to sort by FMTNAME.

The minimum set of variables you will need are FMTNAME, START, END (especially if a range is needed, Label (the result to display) and Type (C or N for character or numeric).

Re: Format Variable based on description in csv file

bikash — Thu, 19 Feb 2015 16:30:56 GMT

Thanks to every one for help. SAS Code provided by Tom solves all my problem.

Bikash