hello,   Do you know how to fill value with zero and reach a tens digit? for table have   have CIK 915913 1029506 3673 75677 0001091530 I expect to get table want want CIK 0000915913 0001029506 0000003673 0000075677 0001091530   thanks in davance. data table1; infile cards truncover; input CIK $100. ; cards; 915913 1029506 3673 75677 0001091530 ; ... View more

Dear all,   How to merge six datasets based on following conditions?   I have six datasets, each of them contains three volumes, ID, SP_ID, Flag_&no.. The Flag_&no. equals 1 if ID has a corresponding SP_ID on the same line. There are sample of four tables here. And I would like to merge them together. Tabl1     ID SP_ID Flag_1 1 a 1 3 c 1 4 f 1 4 h 1   Table2     ID SP_ID Flag_2 1 a 1 3 m 1 4 h 1 4 f 1   Table3     ID SP_ID Flag_3 5 n 1   Table4     ID SP_ID Flag_4 6 q 1     I would like to get the table like below   want           ID SP_ID Flag_1 Flag_2 Flag_3 Flag_4 1 a 1 1     2           3 c 1       3 m   1     4 f 1 1     4 h 1 1     5 n     1   6 q       1   Please note the following situations in the table For ID 1, the SP_ID is ‘a’ in both table 1 and table 2. For ID 2, non-table has corresponding SP_ID. For ID 3, SP_ID are ‘c’ in table1 and ‘m’ in table2. For ID 4, it has two corresponding SP_ID (ie., ‘f’,’h’) in both table 1 and table 2.   Do you know what should I do?   data table1; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 1,a,1 3,c,1 4,f,1 4,h,1 ;;;; run; data table2; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 1,a,1 3,m,1 4,h,1 4,f,1 ;;;; run; data table3; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 5,n,1 ;;;; run; data table4; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 6,q,1 ;;;; run;   ... View more

hello, Do you know how to run a SQL in Macro?   I am running a macro code %MACRO merge(variable=); data Datastream_&variable. (keep=DATASTREAM_ID &variable.); set Work.Datastream_id2; run; data SP_&variable. (keep=SP_Entity_ID SP_&variable.); set WORK.SP_TABLE; run; proc sql; create table Data_SP_&variable. as select distinct DATASTREAM_ID, &variable., SP_Entity_ID from Datastream_&variable. as da join SP_&variable. as sp on da.&variable.=sp.SP_&variable. ; quit; %MEND merge; %merge(variable=CUSIP) run; the log is  NOTE: There were 11853 observations read from the data set WORK.DATASTREAM_ID2. NOTE: The data set WORK.DATASTREAM_CUSIP has 11853 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.14 seconds cpu time 0.01 seconds NOTE: There were 7946 observations read from the data set WORK.SP_TABLE. NOTE: The data set WORK.SP_CUSIP has 7946 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.14 seconds cpu time 0.03 seconds NOTE: Table WORK.DATA_SP_CUSIP created, with 0 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds the last dataset cannot be created, but it did not show why it is failed. Could you please give me some suggestions about this?   thanks in advance. ... View more

Hello,   I am trying to split the value in the same cell to different cells but in the same column.   For example, for table 1 No Code 1 XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB 2 NYSE:IAB; DB:3NL   I expect to have table 2  No Code 1 XTRA:TLB 1 OTCPK:TLMO.F 1 LSE:0R0N 1 WBAG:TLB 1 DB:TLB 2 NYSE:IAB 2 DB:3NL   the strings in table 1 are separated by ";", however, I do not know how to convert them as table 2.   Could you please give me some suggestion about this? Thanks in advance.   data table1; infile cards dlm=,; input No: $2. Code: $20.; cards; "1","XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB" "2","NYSE:IAB; DB:3NL" ; run; ... View more

Hi,   I want to create the descending sequence number of table 1, especially, if two number are equal, then they should be recorded with the same sequence number (just like two '90' are order 2 and two '88' are order 4).    I tried the code like  order =_N_; but it cannot figure out the problem like "two '90' are recorded as 2 and two '88' are recorded as  4"   Could you please give me some suggestions about this?   table 1 number  100 90 90 88 88 87 I expect to get table 2 like   number  order  100 1 90 2 90 2 88 4 88 4 87 6   ... View more

Hi,   I use the following code to create the Chart, proc sgscatter data=number1; title "Average number of Patents and R&D for company in each country, 1990-2012"; compare x=(average_RD_ctry) y=(average_appln_ctry) / group=PERSON_CTRY_CODE; label average_RD_ctry='Average R&D'; label average_appln_ctry='Average number of patent'; run; title; the name of each point  (such as "AU") is shown under the chart. however, I expect to put them with the point in the char   Could you please give me some suggestion about this?   thanks in advance. ... View more

Hi all,   I use the following code to create a list of  histograms and a distribution table    title 'The distribution of patent applied by companies in different industries'; proc sgpanel data=step7.Data_pat_matchresult93_21; panelby Division / uniscale=row; histogram sum_appln_hrm; density sum_appln_hrm; LABEL sum_appln_hrm='number of patents applied by the company'; run; proc means data=step7.Data_pat_matchresult93_21; class Division; var sum_appln_hrm; run; the following are two  histograms,   and  Analysis Variable : sum_appln_hrmDivision N Obs N Mean Std Dev Minimum MaximumA 36B 261C 235D 8309E 480F 273G 145H 262I 1677J 8 36 20.9166667 50.3577913 1.0000000 264.0000000 261 20.8888889 56.8066822 1.0000000 542.0000000 235 28.7021277 91.0871460 1.0000000 1042.00 8309 206.9959081 1524.66 1.0000000 70641.00 480 66.8791667 336.3023201 1.0000000 4051.00 273 40.6483516 227.8560420 1.0000000 2909.00 145 14.9172414 66.6418186 1.0000000 754.0000000 262 26.7442748 128.8751492 1.0000000 1630.00 1677 133.6833631 2142.31 1.0000000 71910.00 8 186.1250000 515.9450241 1.0000000 1463.00   is there any method to put them together? I expect to put the 'N obs', 'N', 'Mean', 'Min' and 'Max' on the top right of each  histogram. Could you pelase give me some suggestions about this? thanks in advance.  ... View more

hello all,   for the table have like below, Type year WC01201 28221H 1990 9598 28221H 1991 4651 28221H 1992 6174 28221H 1993 16307 28221H 1994 12497 28221H 1995 9063 28221H 1996 8067 28221H 1997 19637 28221H 1998 2445 28221H 1999 5101 28221H 2000 2222 28221H 2001 . 28221H 2002 . 28221H 2003 . 28221H 2004 . 28221H 2005 . 28221H 2006 . 28221H 2007 . 28221H 2008 . 28221H 2009 . 28221H 2010 . 28221H 2011 . 28221H 2012 . 8912GT . . 307059 . . 413321 . .   For each ID in the Type variable (eg., '8912GT'), if the value of 'Year' is '.' then I expect to write the value from '1990' to '2012' into it, and expect to get the result like below,   Type year WC01201 28221H 1990 9598 28221H 1991 4651 28221H 1992 6174 28221H 1993 16307 28221H 1994 12497 28221H 1995 9063 28221H 1996 8067 28221H 1997 19637 28221H 1998 2445 28221H 1999 5101 28221H 2000 2222 28221H 2001 . 28221H 2002 . 28221H 2003 . 28221H 2004 . 28221H 2005 . 28221H 2006 . 28221H 2007 . 28221H 2008 . 28221H 2009 . 28221H 2010 . 28221H 2011 . 28221H 2012 . 8912GT 1990 . 8912GT 1991 . 8912GT 1992 . 8912GT 1993 . 8912GT 1994 . 8912GT 1995 . 8912GT 1996 . 8912GT 1997 . 8912GT 1998 . 8912GT 1999 . 8912GT 2000 . 8912GT 2001 . 8912GT 2002 . 8912GT 2003 . 8912GT 2004 . 8912GT 2005 . 8912GT 2006 . 8912GT 2007 . 8912GT 2008 . 8912GT 2009 . 8912GT 2010 . 8912GT 2011 . 8912GT 2012 . 307059 1990 . 307059 1991 . 307059 1992 . 307059 1993 . 307059 1994 . 307059 1995 . 307059 1996 . 307059 1997 . 307059 1998 . 307059 1999 . 307059 2000 . 307059 2001 . 307059 2002 . 307059 2003 . 307059 2004 . 307059 2005 . 307059 2006 . 307059 2007 . 307059 2008 . 307059 2009 . 307059 2010 . 307059 2011 . 307059 2012 . 413321 1990 . 413321 1991 . 413321 1992 . 413321 1993 . 413321 1994 . 413321 1995 . 413321 1996 . 413321 1997 . 413321 1998 . 413321 1999 . 413321 2000 . 413321 2001 . 413321 2002 . 413321 2003 . 413321 2004 . 413321 2005 . 413321 2006 . 413321 2007 . 413321 2008 . 413321 2009 . 413321 2010 . 413321 2011 . 413321 2012 .   I have no idea about it, could you please give me some suggestions about it? data Have; INFILE CARDS DSD DLM=','; input Type :$1000. Yaer :4. WC01201 :10.; cards; 28221H,1990,9598 28221H,1991,4651 28221H,1992,6174 28221H,1993,16307 28221H,1994,12497 28221H,1995,9063 28221H,1996,8067 28221H,1997,19637 28221H,1998,2445 28221H,1999,5101 28221H,2000,2222 28221H,2001, 28221H,2002, 28221H,2003, 28221H,2004, 28221H,2005, 28221H,2006, 28221H,2007, 28221H,2008, 28221H,2009, 28221H,2010, 28221H,2011, 28221H,2012, 8912GT,, 307059,, 413321,, ; RUN;  thanks in advance. ... View more

Hi all,   I would like to define the value of " Variable_name" as "WC01201"     %macro DatastreamVar(variable=); data &variable.1(drop=AA AB AC AD Code NAME); length Type : $50. Variable_name :$100. ; set &variable.; Variable_name=‘&variable.’; if _1990 = 'NA' then _1990 =.; run; %MEND DatastreamVar; %DatastreamVar(variable=WC01201) run; however, it shows as " &variable. " could you please give me some suggestions about this? thanks in advance.   ... View more