I have two tables that I would like to match and I have the following code: proc sql;   create table want as   select distinct *   from have1 a  as a left join        have 2 as b on a.ID=b.ID; quit;   The problem is the ID in HAVE1 is of the sort: 001003 and in table 2 1003. The format of both is Character $6. Why I can't match them. How hcan I convert the IDs to be the same. Format is the same but there are zeros in front of the IDs in have 1.   ... View more

I have the following table and would like an indicator variable equal to one when there is at least one competitor in the same industry in the same state in the same year. Basically it is by competition in the same industry by state. Could you please let me know the SAS SQL Proc code: Have table: ID Year Industry STATE 1 1989 111110 CA 1 1990 111110 CA 1 1991 111110 CA 1 1992 111110 CA 2 1989 111111 AR 2 1990 111111 AR 2 1991 111111 AR 3 1989 111110 CA 3 1990 111110 CA 3 1991 111110 CA 3 1992 111110 CA 3 1993 111110 CA 3 1994 111110 CA 3 1995 111110 CA 3 1996 111110 CA   Want table: ID Year Industry STATE DUMMY 1 1989 111110 CA 1 1 1990 111110 CA 1 1 1991 111110 CA 1 1 1992 111110 CA 1 2 1989 111111 AR 0 2 1990 111111 AR 0 2 1991 111111 AR 0 3 1989 111110 CA 1 3 1990 111110 CA 1 3 1991 111110 CA 1 3 1992 111110 CA 1 3 1993 111110 CA 0 3 1994 111110 CA 0 3 1995 111110 CA 0 3 1996 111110 CA 0   ... View more

I have two tables- have1 and have 2 and would like to append them. Basically keep observations from both tables and sort them first based on ID and second based on YEAR. Do you guys know the SAS SQL code for this append/merge? I prefer SQL and not data step code. Thanks. Have1     ID State YEAR 1 AR 1989 1 AR 1990 1 AR 1991 1 AR 1992 1 AR 1993 1 AR 1994 2 OK 1995 2 OK 1996 2 OK 1997 2 OK 1998   Have2     ID State YEAR 1 CA 1999 1 CA 2000 1 CA 2001 1 CA 2002 1 CA 2003 2 NV 1999 2 NV 2000 2 NV 2001 2 NV 2002 2 NV 2003   Want      ID State YEAR 1 AR 1989 1 AR 1990 1 AR 1991 1 AR 1992 1 AR 1993 1 AR 1994 1 CA 1999 1 CA 2000 1 CA 2001 1 CA 2002 1 CA 2003 2 OK 1995 2 OK 1996 2 OK 1997 2 OK 1998 2 NV 1999 2 NV 2000 2 NV 2001 2 NV 2002 2 NV 2003 ... View more

I am trying to use the SAS SQL procedure to download some data from a table. I have a table that has a column with a variable TLCF (Tax Loss). How can I create a SQL Proc that creates an indicator variable when TLCF>0 in any of the previous years. I know the case for the current year. I use the command: case when TLCF>0 then 1 else 0 end asTLCF. What is the SQL code to tell that I want this a 1 if in any of the previous three years there was a Loss.   ... View more

Dear SAS community: I have the following table: ID Col1 Col2 Col3 1   0   2   0   3 0   Yes 4 0     5   40   6       7 50   No 8       9 10     10       11 0     12   0     I would like to create a new Table: that has just two columns ID and Sum: I would like for Col1 and Col2 to  count values greater than 0 as 1 and missing and zero values as zeros. I would like for Col 3 to treat 'Yes" values as 1 and No and missing values as Zeros. The want table is the following:           ID Col1 Col2 Col3 Sum 1 0 0 0 0 2 0 0 0 0 3 0 0 1 1 4 0 0 0 0 5 0 1 0 1 6 0 0 0 0 7 1 0 1 2 8 0 0 0 0 9 1 0 0 1 10 0 0 0 0 11 0 0 0 0 12 0 0 0 0 ... View more

I have the following data: How can I collapse these two rows into 1 using the SQL procedure:   ID Type Rate Amt Trend event final carryover comment 1 A 2           Text1 1 B 0.7   decreasing 0.625     Text2 ... View more