Date:         Wed, 13 Aug 2003 10:40:40 -0400
Reply-To:     Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject:      Re: Messy codes and still don't get what I need
Comments: To: Mai To <Mai.To@UTH.TMC.EDU>
Content-Type: text/plain

Mai,

A good deal of your trouble is due to the fact that you use the variable
names (code1 code2) to hold the code type.  It is not clear to me whether
the code type has any meaning, but I have preserved it as data.

data fmtdata ;
   retain fmtname "$lkup" ;
   input start $ label $ ;
cards ;
01              11000
02              11100
03              11110
04              11111
0567            22000
0568            22200
0569            22220
0570            22222
;

proc format cntlin = fmtdata ;
run ;

data w ( keep = code codetype amt dept ) ;
   input code1 $ code2 $ amt :comma. ;
   if missing (code1) then
   do ;
      code = code2 ;
      codetype = 2 ;
   end ;
   else
   do ;
      code = code1 ;
      codetype = 1 ;
   end ;
   dept = put ( code , $lkup. ) ;
cards ;
01              .                15.50
02              .                27.30
.               0567            (22.50)
03               .               37.00
.               0569            (40.00)
;

proc sort data = w ;
   by descending codetype code ;
run ;

proc print data = w ;
   by descending codetype code ;
   var code codetype dept amt ;
   sum amt ;
run ;

IanWhitlock@westat.com

-----Original Message-----
From: Mai To [mailto:Mai.To@UTH.TMC.EDU]
Sent: Wednesday, August 13, 2003 9:54 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Messy codes and still don't get what I need


I have these two datasets:

Combine Dept
01              11000
02              11100
03              11110
04              11111
0567            22000
0568            22200
0569            22220
0570            22222


Dataset2:
The flat file looks like this:
Code1           Code2           Amt
01                              15.50
02                              27.30
                0567            (22.50)
03                              37.00
                0569            (40.00)


These are my SAS code:

DATA DEPT;
  INFILE DEPT;
    INPUT @  1 CODE1    $4.
            @  1 CODE2    $4.
            @ 10 DEPT   $5.;

PROC SORT DATA=DEPT; BY CODE1;

DATA DEPT2; SET DEPT; BY CODE1;
PROC SORT DATA=DEPT2; BY CODE2;


DATA INC;
  INFILE INC;
    INPUT @  5 CODE1    $4.
            @ 10 CODE2    $4.
            @ 15 AMT    6.2;

PROC SORT DATA=INC; BY CODE1;

DATA STEP1;
   MERGE DEPT (IN=D) INC(IN=I);  /*THIS STEP TO GET THE DEPT FOR CODE1*/

       BY CODE1;
       IF I;

PROC SORT DATA=STEP1; BY CODE2;

DATA STEP2;
   MERGE DEPT (IN=D) STEP2(IN=I);  /*THIS STEP TO GET THE DEPT FOR CODE2*/
       BY CODE2;
       IF I;

PROC SORT DATA=STEP2; BY CODE1 CODE2;

PROC PRINT;
SUM AMT;
VAR CODE1 CODE2 DEPT AMT;


Somehow the DEPT is missing in a couple of places.  I don't know is there a
less messy way to achieve what I need or not.

I would appreciate all the help.  Thanks.

Mia