topic Re: proc sql in SAS Programming

proc sql

mehak — Mon, 23 Oct 2017 15:13:29 GMT

Can anyone tell me how can i write churners and labels in proc sql .?????
Basically i have two seperate files . one file named as USAGE DATA and the other one named as Churners and labels .

I have imported both the files but dnt know how to write churners and labels in proc sql.

*I HAVE IMPORTED BOTH THE FILES LIKE THIS WAY :

libname _class_s "/folders/myfolders/1";

PROC IMPORT DATAFILE="/folders/myfolders/1/SB.csv"

OUT=_class_s.SB

DBMS=CSV

REPLACE;

RUN;

SAMPLE DATA OF BOTH THE FILES ARE AS

CHURNERS AND LABELS :-

PRODUCT_ID	churn_month
102220594	201109
102220594	201109
102220594	201109
102240104	201105
102240104	201105
102240104	201105
112110440	201107

USAGE DATA :-

PRODUCT_ID	Year	AON	CELL_CALL_CNT_M1	CELL_CALL_CNT_M2	CELL_CALL_CNT_M3	CELL_CALL_CNT_M4	CELL_CALL_CNT_M5	CELL_CALL_CNT_M6
1.02E+08	2009	6	0	0	0	0	0	0
1.02E+08	2010	14	0	0	0	0	0	0
1.02E+08	2011	11	53	47	51	51	7	7
1.02E+08	2009	10	0	0	0	0		0

Re: proc sql

Reeza — Mon, 23 Oct 2017 14:53:20 GMT

It's not clear at all what you have and what you want.

It would also help if you provide your data as a DATA STEP. There are instructions here on how to set that up:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: proc sql

mehak — Mon, 23 Oct 2017 15:11:51 GMT

basically i want to know how to add labels and churners in proc sql

Re: proc sql

Reeza — Mon, 23 Oct 2017 15:54:40 GMT

@mehak wrote:

basically i want to know how to add labels and churners in proc sql

What's a churners?

What do you mean by labels?

Please remember to post more details with your questions. Showing an example helps.

Re: proc sql

HB — Mon, 23 Oct 2017 16:00:44 GMT

I'm with @Reeza . I have no idea what you want.

I can say that anytime data is organized like

CELL_CALL_CNT_M1
CELL_CALL_CNT_M2
CELL_CALL_CNT_M3
CELL_CALL_CNT_M4
CELL_CALL_CNT_M5
CELL_CALL_CNT_M6
etc

that is a suggestion it may not be normalized and may be difficult to work with.

Re: proc sql

mehak — Mon, 23 Oct 2017 16:03:16 GMT

I AM HAVING 3 FILES . i HAVE ATTACHED THE SAMPLE OF ALL THE FILES.

Basically i have to do the following task on this :

import usage data and provide labels from chuners and labels file .

I am able to import the data but i dnt know how to provide labels from chuners and labels file

usage data

PRODUCT_ID	Year	AON	CELL_CALL_CNT_M1	CELL_CALL_CNT_M2	CELL_CALL_CNT_M3	CELL_CALL_CNT_M4
102107083	2009	6	0	0	0	0
102107083	2010	14	0	0	0	0
102107083	2011	11	53	47	51	51
102119004	2009	10	0	0	0	0
102119004	2010	13	0	0	6	3
102119004	2011	8	73	113	38	70
102130021	2009	16	193	203	179	177
102130021	2010	21	11	2	77	11
102130021	2011	20	107	109	79	88
102134104	2009	14	0	10	1	0
102134104	2010	19	127	171	123	123
102134104	2011	17	143	354	338	238
102134105	2009	0	6	143	55	369

Churners file

PRODUCT_ID	churn_month
102220594	201109
102220594	201109
102220594	201109
102240104	201105
102240104	201105
102240104	201105
112110440	201107
112110440	201107
112110440	201107
112113214	201104
112113214	201104
112113214	201104
112116660	201104
112116660	201104

Labels data file

Variable	Label
PRODUCT_ID	Line number
Year	Year
AON	Age on network
CELL_CALL_CNT_M1	count of cell calls-April
CELL_CALL_CNT_M2	count of cell calls-May
CELL_CALL_CNT_M3	count of cell calls-June
CELL_CALL_CNT_M4	count of cell calls-July
CELL_CALL_CNT_M5	count of cell calls-August
CELL_CALL_CNT_M6	count of cell calls-September

Re: proc sql

HB — Mon, 23 Oct 2017 16:17:16 GMT

Is this what you want?

data usage_data;
   input product_id $ year aon CELL_CALL_CNT_M1 CELL_CALL_CNT_M2 CELL_CALL_CNT_M3 CELL_CALL_CNT_M4;
datalines;
102107083	2009	6	0	0	0	0
102107083	2010	14	0	0	0	0
102107083	2011	11	53	47	51	51
102119004	2009	10	0	0	0	0
;
run;

data my_usage_data;
	set usage_data;
	label product_id = 'Line number';
	label aon = 'Age on network';
	* etc;
run;

This give us

The SAS System

Line number	year	Age on	CELL_CALL_CNT_M1	CELL_CALL_CNT_M2	CELL_CALL_CNT_M3	CELL_CALL_CNT_M4
Line number	year	network	CELL_CALL_CNT_M1	CELL_CALL_CNT_M2	CELL_CALL_CNT_M3	CELL_CALL_CNT_M4
10210708	2009	6	0	0	0	0
10210708	2010	14	0	0	0	0
10210708	2011	11	53	47	51	51
10211900	2009	10	0	0	0	0

???

Re: proc sql

mehak — Mon, 23 Oct 2017 16:21:52 GMT

yes sir this is what i want .

But i want to implement all these things in my original data named as usage data .

Re: proc sql

Reeza — Mon, 23 Oct 2017 16:27:07 GMT

So, my translation:

Q from Mehak:

I have a data set with labels for column names, called Table B.

I have a master data set, called Table A which has the columns.

I don't know how to apply the labels from Table B to Table A, in a dynamic format, without typing out each label manually.

Here's Table A as a data step:

...

Here's Table B as a data step:

...

Answer:

Not sure why the third table matters. And your question didn't initially include the labels, ergo the lack of clarity.

Here's an example of how that's implemented. If you have issues, post your code and LOG. Good Luck.

*Create label data set;
data label_data_set;
length name label $25.;
name="Sex"; label="Gender"; output;
name="height"; label="Height (in)"; output;
name="weight"; label="Weight (lbs)"; output;
run;
 
 
*Create sample dataset to apply label;
data class;
set sashelp.class;
run;
 
 
*Create macro variable that holds label statement;
proc sql noprint;
select catx("=", name, quote(trim(label)))
  into :label_list separated by " "
from label_data_set;
quit;
 
 
*Display macro variable in log;
%put &label_list.;
 
 
*Apply labels without recreating dataset;
proc datasets library=work;
modify class;
label &label_list.;
run;quit;
 
 
*Print the dataset to display new labels;
proc print data=class label noobs;
run;

Re: proc sql

HB — Mon, 23 Oct 2017 16:31:19 GMT

>implement all these things in my original data named as usage data

data usage_data;
set usage_data;

will do that. But better to look at @Reeza 's solution.