mySQL

/* IMPORT DATA CSV*/
PROC IMPORT DATAFILE=”C:\DATA\STATADASE17\HOSP17.CSV”
OUT=MYDATA DBMS=CSV REPLACE;
GETNAMES=YES;
RUN;

/*OUTPUT DATA CSV*/
PROC EXPORT DATA=NEW
OUTFILE=’c:\Data\NEW.csv’;

/*CREATE NEW TABLE ORDER BY*/
PROC SQL;
CREATE TABLE NEW AS
SELECT* FROM MYDATA,IDX
WHERE PRSN INV
ORDER BY TAG;
QUIT;

/*USE CASE WHEN TO
GREATE NEW VARIABLES*/
PROC SQL;
CREATE TABLE NEW AS
SELECT * ,
CASE
WHEN PRSN LT 10 THEN ‘ZOO’
WHEN PRSN GE 10 THEN ‘BZOO’
END AS PD,
CASE
WHEN PRSN BETWEEN 1 AND 20 THEN ‘GOOD’
WHEN PRSN BETWEEN 21 AND 30 THEN ‘GOODT’
ELSE ‘NOT_BAD’
END AS DPI
FROM MYDATA;
QUIT;

/*USE IN / NOT IN*/
PROC SQL;
CREATE TABLE NEW AS
SELECT * FROM MYDATA
WHERE PRSN NOT IN
(8,11,14,18,24,27,28,44,47,50,
52,56,57,61,62,64,65,78,80,91,
100,109,110,114,117,
124,126,132,
133,136,138,
143,145,148,
150,155,157,
160,164,166,167,
170,173,175,176,178,179,
180,181,183,184,185,186)
;
QUIT;
/*IN STATA*/
drop if inlist(prsn,8,11,186)

/*R*/
indx=c(8,11,14,18,24,27,28,44,47,50,
52,56,57,61,62,64,65,78,80,91,
100,109,110,114,117,
124,126,132,
133,136,138,
143,145,148,
150,155,157,
160,164,166,167,
170,173,175,176,178,179,
180,181,183,184,185,186)
two=one[!one$PRSN%in%indx,]

/* USE EXCEPT IF TWO TABLES HAVE SAME COLS*/
DATA INDX;
INPUT PRSN @@;
CARDS;
2 3 4 5
7 8 10 12
14 15 19
31 84 92
;
QUIT;

PROC SQL;
CREATE TABLE NEW AS
SELECT PRSN FROM MYDATA
EXCEPT
SELECT PRSN FROM INDX;
QUIT;

PROC PRINT DATA=NEW(OBS=20);
VAR PRSN;
RUN;

data management in household data

#stata
cd “c:\data\”
reserve
keep id itm* amt*
outsheet using “c:\data\inc95t.txt”,comma
restore

different y axis unit

Stata
twoway connected y t ||connected var2 t, yaxis(2)

R
par(mar=c(5,4,2,4),bg=’snow2′)
plot(unemp~tse,type=’b',pch=16,
col=4,ylab=”,xaxt=’n',xlab=’Time’)
#minor axis
axis(1,at=stse,labels=stse,
col.axis=1, las=1,tck=-0.009)
#major axis
axis(1,at=ltse,labels=ltse,tck=0.009)
abline(v=1958,col=’purple’,lwd=2)
mtext(‘Unemployment rate’,2,padj=-3.5)
#y2 axis
par(new=T)
plot(GNP~tse,xaxt=’n',ann=F,yaxt=’n',col=2,
xlab=”,ylab=’GNP’,type=’b',pch=15)
axis(4,at=seq(80,120,7),
labels=seq(80,120,7))
mtext(‘GNP’,4,padj=3.5)

/*SAS*/

%LET PATH=C:\DATA;
DATA LONG;
INFILE “&PATH\LONGLY.CSV” DLM=’,';
INPUT GNPD    GNP    Unemp ArmedF Population    Year Employed;
RUN;

goptions reset=all cback=WHITE;
/*V=DOT,STAR,CIRCLE,SQUARE*/

SYMBOL1 C=BLUE V=DOT L=1 W=1 I=JOIN;
SYMBOL2 C=RED V=STAR L=2 W=1 I=JOIN;

/* Create axis definitions  F:TIME FORMAT(‘Helvetica’),
H:HEIGHT*/

AXIS1  LABEL=(F=times H=4 PCT
ANGLE=90 ‘GNP’) OFFSET=(0,0)   ORDER=(200 TO 600 BY 50);
AXIS2  LABEL=(F=times H=4 PCT ANGLE=90 ‘UNEMP’)
OFFSET=(0,0) ORDER=(100 TO 500 BY 50);
AXIS3 ORDER=(1947 TO 1962 BY 2);

/*cREATE LEGEND CONTENT*/
LEGEND1 LABEL=none VALUE=(‘GNP’ ‘unemp’)
position=(top left inside)offset=(3,-1);
LEGEND2 LABEL=none  VALUE=(‘UNEMP’)
position=(top left inside) offset=(3,-2.5);

TITLE F=times H=4 PCT ‘SAS GPLOT’;
PROC GPLOT DATA=LONG;
PLOT GNP*YEAR/OVERLAY VAXIS=AXIS1 HAXIS=AXIS3 LEGEND=LEGEND1 HREF = 1958;
PLOT2 UNEMP*YEAR/OVERLAY VAXIS=AXIS2 LEGEND=legend2;
RUN;
QUIT;