DATA Step, Macro, Functions and more

proc sql

Reply
Contributor
Posts: 30

proc sql

[ Edited ]

 

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_IDchurn_month
102220594201109
102220594201109
102220594201109
102240104201105
102240104201105
102240104201105
112110440201107

 

USAGE DATA :-

 

 

PRODUCT_IDYearAONCELL_CALL_CNT_M1CELL_CALL_CNT_M2CELL_CALL_CNT_M3CELL_CALL_CNT_M4CELL_CALL_CNT_M5CELL_CALL_CNT_M6
1.02E+0820096000000
1.02E+08201014000000
1.02E+082011115347515177
1.02E+082009100000 

0

 

 

 

 

 

 

Super User
Posts: 23,332

Re: proc sql

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-dat...

Contributor
Posts: 30

Re: proc sql

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

Super User
Posts: 23,332

Re: proc sql


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.

 

Super Contributor
Super Contributor
Posts: 266

Re: proc sql

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.

Contributor
Posts: 30

Re: proc sql

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_IDYearAONCELL_CALL_CNT_M1CELL_CALL_CNT_M2CELL_CALL_CNT_M3CELL_CALL_CNT_M4
102107083200960000
1021070832010140000
10210708320111153475151
1021190042009100000
1021190042010130063
10211900420118731133870
102130021200916193203179177
1021300212010211127711
1021300212011201071097988
10213410420091401010
102134104201019127171123123
102134104201117143354338238
10213410520090614355

369

 

 

Churners file 

 

PRODUCT_IDchurn_month
102220594201109
102220594201109
102220594201109
102240104201105
102240104201105
102240104201105
112110440201107
112110440201107
112110440201107
112113214201104
112113214201104
112113214201104
112116660201104
112116660201104

 

 

Labels data file

Variable Label
PRODUCT_IDLine number
YearYear
AONAge on network
CELL_CALL_CNT_M1count of cell calls-April
CELL_CALL_CNT_M2count of cell calls-May
CELL_CALL_CNT_M3count of cell calls-June
CELL_CALL_CNT_M4count of cell calls-July
CELL_CALL_CNT_M5count of cell calls-August 
CELL_CALL_CNT_M6

count of cell calls-September

 

 

 

Super Contributor
Super Contributor
Posts: 266

Re: proc sql

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
network
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

 

???

Contributor
Posts: 30

Re: proc sql

yes sir this is what i want .

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

Super Contributor
Super Contributor
Posts: 266

Re: proc sql

>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.
Super User
Posts: 23,332

Re: proc sql

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;

 

Ask a Question
Discussion stats
  • 9 replies
  • 179 views
  • 0 likes
  • 3 in conversation